Mongo db query For join queries

Question

I have two different object

User
UserGroup

User

{
          _id:1,
          Name:'name',
          Usergroup:
      [

                        {
                                '$ref' : 'UserGroup',
                                '$id' : 1
                         },

                         {
                                '$ref' : 'UserGroup',
                                '$id' : 2
                        }]


 }

UserGroup

{
                         _id:1,
                          name: 'admin'
                  },{
                        _id:2,
                         name: 'admin1'
 }

I need to get usergroup collection along with list of users can anyone help me on writing join query please.

Possible duplicate of [How do I perform the SQL Join equivalent in MongoDB?](https://stackoverflow.com/questions/2350495/how-do-i-perform-the-sql-join-equivalent-in-mongodb) — Bob Kuhar, Nov 01 '18 at 04:50

Nishant Bhardwaz · Answer 1 · 2018-11-01T09:50:00.960

You can use $lookup in mongo to join two collections.

  db.user_collection_name.aggregate(
{
    $project: { 
        group_id: {
          $map: { 
             input: { 
                  $map: {
                      input:"$userGroup",
                      in: {
                           $arrayElemAt: [{$objectToArray: "$$this"}, 1]
                      },
                  }
             },
             in: "$$this.v"}},
             "name":1
        }
}, 
    {$lookup:
         {
           from: userGroup_collection_name,
           localField: "group_id",
           foreignField: _id,
           as: users
         }
    })

Explanation:
Within the aggregator, a DBRef BSON type can be handled like an object, with two or three fields (ref, id, and DB). Therefore we need to abstract key, the value from dbref field. Therefore we need $project function to take Id then we can $lookup using that Id.

I need to get usergroups with list of users for a group. In user collection usergroup is in array.. I tried your example getting error 'field path names may not start with $' — Praveen Kumar, Nov 01 '18 at 05:37

Mongo db query For join queries

1 Answers1