MongoDB aggregation $lookup to parent array field with pipeline syntax and _id as string

user_7475615 0
Viewed 647

I have the following collections in my MongoDB (v.4.4):

guilds:

{
 _id: String,
 members: [{ _id String, rank: number },{ _id String, rank: number }...]
}

and characters:

{
  _id: String,
  many_other: 'fields'
}

And I'd like to join with the $lookup pipeline syntax guild.members <= characters by _id field.

I don't use Mongo build-in OjbectIds as _id, I overwrite it with strings in both collections. As I heard, there is a bit different behaviour with $lookup with strings as _id in pipelines. So make sure, that you knew about it, before answering.

The expected result that I want is simple, I'd like to save rank from the original document, and also add any other field from $lookup it:

{
  _id: String // (guild)
  members: [
    {
      _id: String, // (characters)
      rank: number,
      many_other: '...fields from characters'
    },
    {
      _id: String, // (characters)
      rank: number,
      many_other: '...fields from characters'
    }, ...
]

Mongo Playground example: avaliable

What have I tried:

Various queries, like:

      {
        $lookup: {
          from: "characters",
          pipeline: [
            {
              $match: {
                $expr: { $eq: [ { $toString :"$members._id" }, { $toString : "$$character_id" } ] }
              }
            }
          ],
          as: "guild_members"
        },
      }

With converting ID's to string, with let stages variables, and using $map operator. But I still far away from the required result.

There is also another problem, which is some relevance to the question, but not related to queries itself

As you may see, the characters collection has many other fields. Some of them are pretty heavy, (because guilds can have up to 500 members) which making the result document >16 MB (MongoDB threshold limit) which gives an error. Since, as far as I know, we could not pick fields from joined documents, it will be much better to exclude it right after the $lookup stage or something like that. Any advice about it will be pretty welcome.

mongoose aggregation-framework mongodb javascript
2 Answers

Lookup with pipeline is not required, when you pass members._id as localField,

  • $lookup with characters and pass members._id as localField
  • $map to iterate loop of members array
  • $filter to iterate loop of members_guid and get matching member
  • $arrayElemAt to get first matching element from above filter
  • $mergeObjects to merge current fields with above filtered object of member
db.guilds.aggregate([
  {
    $lookup: {
      from: "characters",
      localField: "members._id",
      foreignField: "_id",
      as: "members_guid"
    }
  },
  {
    $project: {
      members: {
        $map: {
          input: "$members",
          as: "m",
          in: {
            $mergeObjects: [
              "$$m",
              {
                $arrayElemAt: [
                  {
                    $filter: {
                      input: "$members_guid",
                      cond: { $eq: ["$$this._id", "$$m._id"] }
                    }
                  },
                  0
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Playground

user_89871280

I take the @turivishal answer since the old-style-native aggregation is much easier to understand than pipeline syntax. But if someone is interested, my old part of aggregation, which I used a while ago.

db.guilds.aggregate([
  {
    $lookup: {
      from: "characters",
      let: {
        members: "$members"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$_id",
                "$$members._id"
              ]
            }
          }
        },
        {
          $addFields: {
            rank: {
              $reduce: {
                input: "$$members",
                initialValue: null,
                in: {
                  $cond: [
                    {
                      $eq: [
                        "$$this._id",
                        "$_id"
                      ]
                    },
                    "$$this.rank",
                    "$$value"
                  ]
                }
              }
            }
          }
        }
      ],
      as: "members"
    },
    
  }
])
user_74756150
Related