Mongodb aggregation nested lookup

Question

I have 3 collections as follows:

GroupRoles Collection:

//1
{
    "_id": ObjectId("62a384ee0c4dbafc64000fba"),
    "name": "GroupRole template 1",
    "groupRoles": [
        {
            "_id": ObjectId("6298503f8a31000024002107"),
            "members": [
                ObjectId("629e1bb117366c39bc7d78e1")
            ]
        },
        {
            "_id": ObjectId("629850368a31000024002106"),
            "members": [
                ObjectId("629ee6d502877d0f93f5dabe"),
                ObjectId("629ee6d002877d0f93f5dab8")
            ]
        },
        {
            "_id": ObjectId("6298502f8a31000024002105"),
            "members": [ ]
        },
        {
            "_id": ObjectId("629850288a31000024002104"),
            "members": [ ]
        },
        {
            "_id": ObjectId("6298501f8a31000024002103"),
            "members": [ ]
        },
        {
            "_id": ObjectId("629850128a31000024002102"),
            "members": [ ]
        }
    ]
}

Role Collections:

// 1 {"_id": ObjectId("629850128a31000024002102"), "type": 1, "name": "Role 1",}

// 2 {"_id": ObjectId("6298501f8a31000024002103"), "type": 1, "name": "Role 2",}

// 3 {"_id": ObjectId("629850288a31000024002104"), "type": 1, "name": "Role 3",}

// 4 {"_id": ObjectId("6298502f8a31000024002105"), "type": 1, "name": "Role 4",}

// 5 {"_id": ObjectId("629850368a31000024002106"), "type": 1, "name": "Role 5",}

// 6 {"_id": ObjectId("6298503f8a31000024002107"), "type": 1, "name": "Role 6",}

and User Collection:

// 1 {"_id": ObjectId("629e1bb117366c39bc7d78e1"), "email": "[email protected]", "name": "user 01"}

// 2 {"_id": ObjectId("629ee6d502877d0f93f5dabe"), "email": "[email protected]", "name": "user 02"}

// 3 {"_id": ObjectId("629ee6d002877d0f93f5dab8"), "email": "[email protected]", "name": "user 03"}

How can select GroupRoles with output format like this:

{
    "_id": ObjectId("62a384ee0c4dbafc64000fba"),
    "name": "GroupRole template 1",
    "groupRoles": [
        {
            "_id": ObjectId("6298503f8a31000024002107"),
            "type": 1,
            "name": "Role 6",
            "members": [
                {
                    "_id": ObjectId("629e1bb117366c39bc7d78e1"),
                    "email": "[email protected]",
                    "name": "user 01"
                }
            ]
        },
        {
            "_id": ObjectId("629850368a31000024002106"),
            "type": 1,
            "name": "Role 5",
            "members": [
                {
                    "_id": ObjectId("629ee6d502877d0f93f5dabe"),
                    "email": "[email protected]",
                    "name": "user 02"
                },
                {
                    "_id": ObjectId("629ee6d002877d0f93f5dab8"),
                    "email": "[email protected]",
                    "name": "user 03"
                }
            ]
        },
        {
            "_id": ObjectId("6298502f8a31000024002105"),
            "type": 1,
            "name": "Role 4",
            "members": [ ]
        },
        {
            "_id": ObjectId("629850288a31000024002104"),
            "type": 1,
            "name": "Role 3",
            "members": [ ]
        },
        {
            "_id": ObjectId("6298501f8a31000024002103"),
            "type": 1,
            "name": "Role 2",
            "members": [ ]
        },
        {
            "_id": ObjectId("629850128a31000024002102"),
            "type": 1,
            "name": "Role 1",
            "members": [ ]
        }
    ]
}

I've tried aggregation and lookup, but it didn't get the results I wanted.

here is my aggregation query The list of members is not in the correct index of groupRoles

[
  {
    '$lookup': {
      'from': 'roles', 
      'localField': 'groupRoles._id', 
      'foreignField': '_id', 
      'as': 'temp_roles'
    }
  }, {
    '$lookup': {
      'from': 'users', 
      'localField': 'groupRoles.members', 
      'foreignField': '_id', 
      'as': 'temp_users'
    }
  }, {
    '$addFields': {
      'groupRoles': {
        '$map': {
          'input': '$groupRoles.members', 
          'as': 'mems', 
          'in': {
            'members': {
              '$filter': {
                'input': '$temp_users', 
                'as': 'temu', 
                'cond': {
                  '$in': [
                    '$$temu._id', '$$mems'
                  ]
                }
              }
            }
          }
        }
      }
    }
  }, {
    '$addFields': {
      'groupRoles': {
        '$map': {
          'input': {
            '$zip': {
              'inputs': [
                '$groupRoles', '$temp_roles'
              ]
            }
          }, 
          'in': {
            '$mergeObjects': '$$this'
          }
        }
      }
    }
  }, {
    '$unset': [
      'temp_roles', 'temp_users'
    ]
  }, {
    '$facet': {
      'metadata': [
        {
          '$group': {
            '_id': null, 
            'total': {
              '$sum': 1
            }
          }
        }
      ], 
      'data': []
    }
  }, {
    '$project': {
      'data': {
        '_id': 1, 
        'name': 1, 
        'groupRoles': {
          '_id': 1, 
          'type': 1, 
          'name': 1, 
          'members': {
            '_id': 1, 
            'name': 1, 
            'email': 1
          }
        }
      }, 
      'total': {
        '$arrayElemAt': [
          '$metadata.total', 0
        ]
      }
    }
  }
]

please help me find the solution.

Perhaps share your aggregate query to the question, so we can try to reproduce and detect the root cause. — Yong Shun
– Yong Shun, Commented Jun 11, 2022 at 7:34
@YongShun i added the error statement. my members list is in the wrong place in groupRoles. it not mapped with role id. — Hoang An Nguyen
– Hoang An Nguyen, Commented Jun 11, 2022 at 8:05
Demo. The output data look the same as your expected data. Can you further explain what the "list of members is not in the correct index of groupRoles" mean? Is it order by sequence based on the id in original groupRoles.members? — Yong Shun
– Yong Shun, Commented Jun 11, 2022 at 8:32
@YongShun Oh sorry, I omitted the value when I wrote the question, I edited the data in the question. Member list of result not mapped with role id. — Hoang An Nguyen
– Hoang An Nguyen, Commented Jun 11, 2022 at 9:01
Demo in my correct result user :"629e1bb117366c39bc7d78e1" is member of role "6298503f8a31000024002107". but query return user "629e1bb117366c39bc7d78e1" is member of role "629850128a31000024002102". — Hoang An Nguyen
– Hoang An Nguyen, Commented Jun 11, 2022 at 9:08

Yong Shun · Accepted Answer · 2022-06-11 10:43:46Z

Revise your query and thanks for the hints.

The issue is on $zip, which I think this operator is not suitable. You can check the behavior of $zip.

Combine both $addFields stages into one.

Iterate each value in groupRoles and with $mergeObjects for

Current iterate value.
The first match document from the temp_roles array by matching _id.
The document with members array.

db.groupRoles.aggregate([
  {
    "$lookup": {
      "from": "roles",
      "localField": "groupRoles._id",
      "foreignField": "_id",
      "as": "temp_roles"
    }
  },
  {
    "$lookup": {
      "from": "users",
      "localField": "groupRoles.members",
      "foreignField": "_id",
      "as": "temp_users"
    }
  },
  {
    "$addFields": {
      "groupRoles": {
        "$map": {
          "input": "$groupRoles",
          "as": "gr",
          "in": {
            "$mergeObjects": [
              "$$gr",
              {
                $first: {
                  "$filter": {
                    "input": "$temp_roles",
                    "as": "r",
                    "cond": {
                      $eq: [
                        "$$gr._id",
                        "$$r._id"
                      ]
                    }
                  }
                }
              },
              {
                "members": {
                  "$filter": {
                    "input": "$temp_users",
                    "as": "mem",
                    "cond": {
                      "$in": [
                        "$$mem._id",
                        "$$gr.members"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    "$unset": [
      "temp_roles",
      "temp_users"
    ]
  },
  
])

Sample Mongo Playground

Collectives™ on Stack Overflow

Mongodb aggregation nested lookup

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related