Issue with Aggregation from Two Collections in MongoDB

Ciprian_Milan · 2024-09-20T19:27:51.822Z

Hello everyone,

I’m trying to aggregate data from two collections in MongoDB but I’m running into an issue where the tagString and tagObject arrays are coming back empty. Here’s the context:

First, I retrieve a document from the dbChallenges collection:

db.dbchallenges.findOne({ _id: ObjectId("66ea89ef454ef534047b1ba8") }, { tags: 1 });

Result:

{
  _id: ObjectId('66ea89ef454ef534047b1ba8'),
  tags: [
    {
      tagID: ObjectId('66e8a25ee39bd5ecdc6628a8'),
      position: 0,
      _id: ObjectId('66ea89ef454ef534047b1ba9')
    }
  ]
}

Then, I check a document from the dbTags collection:

db.dbtags.findOne({ _id: ObjectId("66e8a25ee39bd5ecdc6628a8") });

Result:

{
  _id: ObjectId('66e8a25ee39bd5ecdc6628a8'),
  ro: 'Tutorial',
  en: 'Tutorial',
  master: true,
  __v: 0
}

Next, I attempt to aggregate the data:

db.dbchallenges.aggregate([
  {
    $match: {
      _id: ObjectId("66ea89ef454ef534047b1ba8"),
    },
  },
  {
    $addFields: {
      tagIDsAsObject: {
        $map: {
          input: "$tags",
          as: "tag",
          in: { $toObjectId: "$$tag.tagID" },
        },
      },
      tagIDsAsString: {
        $map: {
          input: "$tags",
          as: "tag",
          in: { $toString: "$$tag.tagID" },
        },
      },
    },
  },
  {
    $lookup: {
      from: "dbTags",
      let: { tagIdsStr: "$tagIDsAsString" },
      pipeline: [
        {
          $addFields: { _idAsString: { $toString: "$_id" } },
        },
        {
          $match: {
            $expr: {
              $in: ["$_idAsString", "$$tagIdsStr"],
            },
          },
        },
        {
          $project: {
            _id: 1,
            _idAsString: 1,
            ro: 1,
            en: 1
          }
        }
      ],
      as: "tagString",
    },
  },
  {
    $lookup: {
      from: "dbTags",
      localField: "tagIDsAsObject",
      foreignField: "_id",
      as: "tagObject",
    },
  },
  {
    $project: {
      tags: 1,
      tagIDsAsObject: 1,
      tagIDsAsString: 1,
      tagString: 1,
      tagObject: 1,
    },
  },
]);

The result of the aggregation is as follows:

[
  {
    _id: ObjectId('66ea89ef454ef534047b1ba8'),
    tags: [
      {
        tagID: ObjectId('66e8a25ee39bd5ecdc6628a8'),
        position: 0,
        _id: ObjectId('66ea89ef454ef534047b1ba9')
      }
    ],
    tagIDsAsObject: [ ObjectId('66e8a25ee39bd5ecdc6628a8') ],
    tagIDsAsString: [ '66e8a25ee39bd5ecdc6628a8' ],
    tagString: [],
    tagObject: []
  }
]

As you can see, both tagString and tagObject are empty, and I can’t understand what I’m doing wrong. Any insights or suggestions would be greatly appreciated!

Thank you!

steevej · 2024-09-20T19:52:56.596Z

Ciprian_Milan:

{
    $lookup: {
      from: "dbTags",
      localField: "tagIDsAsObject",
      foreignField: "_id",
      as: "tagObject",
    },
  },

should be

{
    $lookup: {
      from: "dbTags",
      localField: "tags.tagID",
      foreignField: "_id",
      as: "tagObject",
    },
  },

You do not need all the $map and $toObjectId or $toString that you do.

Another thing is that sometimes you use dbTags and at other time you use dbtags. Collection names are case sensitive. So if your collection is really dbtags like dbChallanges is dbchallanges then it is normal that the result is empty.

So if you really really needs the $map/$toObjectId/$toString then simply try to replace from: “dbTags” to from: “dbtags” in the $lookup.

steevej · 2024-09-25T14:08:23.058Z

@Ciprian_Milan, if the issue is anything else than the typo dbTags rather than dbtags, then please share the solution with us in order to help others whom may face the same challenge.

If the following both works:

Ciprian_Milan:

db.dbtags.findOne({ _id: ObjectId("66e8a25ee39bd5ecdc6628a8") })

and

Ciprian_Milan:

db.dbchallenges.findOne({ _id: ObjectId("66ea89ef454ef534047b1ba8") },

then the following will definitively not work

Ciprian_Milan:

from: "dbTags",

and the following should work

steevej:

simply try to replace from: “dbTags” to from: “dbtags” in the $lookup

and could be marked as the solution.

steevej · 2024-10-02T12:49:42.660Z

@Ciprian_Milan, please do not leave your thread to die.

If the issue was simply the case sensitive issue, like we all do, please mark my post as the solution.

Knowing that it is a frequent issue will help others find their own typos faster.

Thanks