MongoDB Aggregate Pipeline query

Question

I'm using mongoDB 3.6 on node.js 8.11.1 and working with MongoDB Node.js Driver. I have two collections, 'group' and 'user':

group:
[  
   {  
      "_id":1,
      "groupName":"group1",
      "users":[  
         {  
            "userId":1,
            "isAdmin":"false"
         },
         {  
            "userId":2,
            "isAdmin":"true"
         }
      ]
   },
   {  
      "_id":2,
      "groupName":"group2",
      "users":[  
         {  
            "userId":2,
            "isAdmin":"false"
         },
         {  
            "userId":3,
            "isAdmin":"true"
         }
      ]
   }
]

user:
[  
   {  
      "_id":1,
      "username":"user1",
      "firstname":"a",
      "lastname":"aa",
      "mobileNo":"+1111111"
   },
   {  
      "_id":2,
      "username":"user2",
      "firstname":"b",
      "lastname":"bb",
      "mobileNo":"+2222222"
   },
   {  
      "_id":3,
      "username":"user3",
      "firstname":"c",
      "lastname":"cc",
      "mobileNo":"+3333333"
   }
]

I need an aggregate to return something like this:

[  
   {  
      "_id":1,
      "groupName":"group1",
      "members":[  
         {  
            "isAdmin":"false",
            "username":"user1",
            "firstname":"a",
            "lastname":"aa"
         },
         {  
            "isAdmin":"true",
            "username":"user2",
            "firstname":"b",
            "lastname":"bb"
         }
      ]
   },
   {  
      "_id":2,
      "groupName":"group2",
      "members":[  
         {  
            "isAdmin":"false",
            "username":"user2",
            "firstname":"b",
            "lastname":"bb"
         },
         {  
            "isAdmin":"true",
            "username":"user3",
            "firstname":"c",
            "lastname":"cc"
         }
      ]
   }
]

At "members" in result, "isAdmin" return from "users" at group collection and "username", "firstname" and "lastname" came from user collection

Many thanks,

Milad.

Answer 1

You can try below aggregation from mongodb 3.6 and above

db.group.aggregate([
  { "$unwind": "$users" },
  { "$lookup": {
    "from": Users.collection.name,
    "let": { "userId": "$users.userId", "isAdmin": "$users.isAdmin" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": [ "$_id", "$$userId" ] } } },
      { "$project": { "isAdmin": "$$isAdmin", "username": 1, "firstName": 1, "lastName": 1 }}
    ],
    "as": "members"
  }},
  { "$unwind": "$members" },
  { "$group": {
    "_id": "$_id",
    "members": { "$push": "$members" },
    "groupName": { "$first": "$groupName" }
  }}
])