Question 291351 in Mongodb Question posted in
The official documentation can be found here.

How to lookup a sub-array in mongodb?

I tried to use aggregate and lookup but look like I cannot keep role in the members item. Any idea how to resolve it?

db.groups.insertMany([
    {
        title: 'Hi',
        members: [
            {
                userId: ObjectId('62589515f239750e7e44b958'), 
                role: 'Admin'
            },
            {
                userId: ObjectId('655f634c632d0c23b9d455de'), 
                role: 'Member'
            }
        ]
    }
])

and

db.users.insertMany([
    {
        _id: ObjectId('62589515f239750e7e44b958'), 
        name: 'A',
    },
    {
        _id: ObjectId('655f634c632d0c23b9d455de'), 
        name: 'B',
    }
])

How do we can have a result like this by using aggregate, many thanks:

{
    title: 'Hi',
    members: [
        {
            user: {
                _id: ObjectId('62589515f239750e7e44b958'), 
                name: 'A',
            },
            role: 'Admin'
        },
        {
            user: {
                _id: ObjectId('655f634c632d0c23b9d455de'), 
                name: 'B',
            },
            role: 'Member'
        },
    ]
}
Tags:

2

Answers


  1. 0

    $unwind the members and perform $lookup. Wrangle the object a bit and $group to reform the object.

    db.groups.aggregate([
  {
    "$unwind": "$members"
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "members.userId",
      "foreignField": "_id",
      "as": "usersLookup"
    }
  },
  {
    "$unwind": "$usersLookup"
  },
  {
    "$set": {
      "members": {
        "user": "$usersLookup",
        "role": "$members.role"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "title": {
        $first: "$title"
      },
      "members": {
        "$push": "$members"
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    The issue with the lookup is that if you insert the new field into an existing array it replaces that array, rather than adding the result to each object in the array. The Mongo docs provide a similar example with $lookup and $mergeObjects. The key to using it effectively in your case is to $unwind the members first. And at the end, un-unwind (rewind? windup?) the objects and add the members.user to a single array.

    db.groups.aggregate([
  {
    "$unwind": "$members"
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "members.userId",
      "foreignField": "_id",
      "as": "members.user"
    }
  },
  {
    // convert list of one user to just user object
    "$set": {
      "members.user": {
        "$arrayElemAt": [
          "$members.user",
          0
        ]
      }
    }
  },
  {
    // your example output doesn't have this field
    "$unset": "members.userId"
  },
  {
    // un-unwind / rewind / windup
    "$group": {
      "_id": "$_id",
      "members": {
        "$push": "$members"
      },
      // add all remaining fields
      "title": {
        "$first": "$title"
      }
    }
  }
])

    Mongo Playground

    PS. Needs a more elegant solution to choose all the "other" fields in the final $group. Right now, "title" is the only one so it’s fine but maybe something with a $project would work better for those cases.

    For the curious, the difference between this and Ray’s answer is that I lookup into the members object rather than at the root level. If there were many fields in each object of members, then this pipeline doesn’t require setting each of the other fields.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search