How to join and group user list results with values from array in another collection?

Question

I am new to MongoDB and am trying to retrieve a list of users that contains their user roles which are stored in the DB as a separate collection to the users collection.

I have a users collection, sample document below:

/* 1 */
{
 "_id": "cf67e695-ea52-47a8-8e42-b95b863a2b69",
 "DateCreated": ISODate("2018-11-11T21:41:37.125Z"),
 "Email": "user@email.com",
 "FirstName": "John",
 "LastName": "Does",
 "Language": "en",
 "TimeZoneId": null,
 "Roles": [
   "c2ee344f-48b7-4c46-9392-853c6debd631",
   "ada94631-af8c-43e9-a031-de62ffae1d20"
 ],
 "Status": 0
}

I also have a Roles collection, sample document below:

{
 "_id": "c2ee344f-48b7-4c46-9392-853c6debd631",
 "DateCreated": ISODate("2018-11-14T10:58:27.053Z"),
 "Name": "Administrator 2",
 "Description": " View only but able to manage their own users (credentials). They do have update capabilities"
}

What I want is to retrieve a list that shows the user friendly name of the role and not the _id value of each role that is currently stored against the User Profile. I'm close but not quite there.

Using the following, I am able to get the results of the Roles list but ONLY the role Names. What I want is the full User profiles with the user friendly names attached with each document. Here is my syntax so far:

db.Users.aggregate([
 {
   $match: {
     "Email": /@email.com/
   }
 },
 {
   $project: {
     _id: 1,
     Email: 1,
     FirstName: 1,
     Roles: 1
   }
 },
 {
   $unwind: {
     path: "$Roles"
   }
 },
 {
   $lookup: {
     from: "Roles",
     localField: "Roles",
     foreignField: "_id",
     as: "Roles"
   }
 },
 {
   $group: {
     _id: "$_id",
     Roles: {
       $push: "$Roles.Name"
     }
   }
 }
])

For whatever reason I am not getting the Email, FirstName and LastName fields returned in the results. All I get currently is:

{
 "_id": "639ba2b6-fc80-44f4-8ac0-0a92d61099c4",
 "Roles": [
   [
     "Administrator 2"
   ],
   [
     "Administrators"
   ]
 ]
}

I would like to get something like:

{
 "_id": "cf67e695-ea52-47a8-8e42-b95b863a2b69",
 "DateCreated": ISODate("2018-11-11T21:41:37.125Z"),
 "Email": "user@email.com",
 "FirstName": "John",
 "LastName": "Does",
 "Language": "en",
 "TimeZoneId": null,
 "Roles": [
   "Administrators",
   "Administrator 2"
 ],
 "Status": 0
}

Any help, much appreciated!

The result you are looking for is actually the default behavior of [`$lookup`](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) and without needing any other stages. Most importantly you **do not need** to `$unwind` before you do the `$lookup`. You probably go the idea from the second marked duplicate above, but you did not really follow the example exactly NOR did you read on to realize this process was working around in issue raised in [SERVER-22881](https://jira.mongodb.org/browse/SERVER-22881) which has **long** since been resolved. — Neil Lunn, Apr 01 '19 at 09:38
i.e After the `$lookup` ( once corrected and with no `$unwind` ) you can just do `{ "$addFields": { "Roles": "$Roles.Name" } }` — Neil Lunn, Apr 01 '19 at 09:40
Thanks @NeilLunn, I will work on improving the query - thanks for letting me know — user11194099, Apr 02 '19 at 03:27

score 0 · Answer 1 · answered Apr 01 '19 at 07:02

Add this code

db.users.aggregate([
   {
      $unwind: "$Roles"
   },
   {
      $lookup:
         {
            from: "roles",
            localField: "_id",
            foreignField: "Roles",
            as: "Roles"
        }
   }
]).exec((err, result) => {
    console.log(result);
})

score 0 · Accepted Answer · answered Apr 01 '19 at 07:16

You have to include those fields in your $group pipeline, Please just update your $group with below:

$group: {
 _id: "$_id",
 FirstName: {$first:"$FirstName"},
 Email: {$first:"Email"},
 Roles: {
   $push: "$Roles.Name"
 }

}

you can add more fields you need using $first in $group. I have tried with the given sample document and its working for me.

How to join and group user list results with values from array in another collection?

2 Answers2