How can I group by a single field in mongo?

Question

Two part question

1. I would like to group records by a single field.

2. then retrieve user information

I would like to group by the REF_NotifierID field.

Here is the data.

[
{
  "_id": "66d90957373dd9d5c85d503c",
  "REF_NotifierID": "66d9070f373dd9d5c85d5034",
  "REF_UserID": "66c8fbb1a97fee2ae10a3d81",
},
{
  "_id": "66d90a33373dd9d5c85d5050",
  "REF_NotifierID": "66d90700373dd9d5c85d502e",
  "REF_UserID": "66c8fbb1a97fee2ae10a3d81",
},
{
  "_id": "66d9ca6be74061bbd0d1996a",
  "REF_NotifierID": "66d90700373dd9d5c85d502e",
  "REF_UserID":"66d9c3ddc1d6fcc235ccc92e",
}
]

Part 1

Here is what I have tried... didn't work :-(

$group : {
  REF_NotifierID: {
    $first: "$REF_NotifierID",
    $users: "REF_UserID",
  }
}

This is the only data that I would like to have returned and in this structure:

{
  "notifiers": [
    {
      "REF_NotifierID": "66d9070f373dd9d5c85d5034",
      "users": [
        {
          "REF_UserID": "66c8fbb1a97fee2ae10a3d81"
        }
      ]
    },
    {
      "REF_NotifierID": "66d90700373dd9d5c85d502e",
      "users": [
        {
          "REF_UserID": "66c8fbb1a97fee2ae10a3d81"
        },
        {
          "REF_UserID": "66d9c3ddc1d6fcc235ccc92e"
        }
      ]
    }
  ]
}

thanks to @cmgchesss ! this groups correctly

  {
    "$group": {
      "_id": "$REF_NotifierID",
      "users": {
        "$addToSet": {
          "REF_UserID": "$REF_UserID"
        }
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "notifiers": {
        "$push": {
          "REF_NotifierID": "$_id",
          "users": "$users"
        }
      }
    }
  },
  {
    "$unset": "_id"
  }

Part 2

I would like to get the user information from the grouped results

I have tried a lookup

    $lookup: {
      from: 'users',
      localField: 'REF_UserID',
      foreignField: '_id',
      as: 'testusers',
    },

this is what I get:

[
        {
            "notifiers": [
                {
                    "REF_NotifierID": "66d9070f373dd9d5c85d5034",
                    "users": [
                        {
                            "REF_UserID": "66c8fbb1a97fee2ae10a3d81"
                        }
                    ]
                },
                {
                    "REF_NotifierID": "66d90700373dd9d5c85d502e",
                    "users": [
                        {
                            "REF_UserID": "66c8fbb1a97fee2ae10a3d81"
                        },
                        {
                            "REF_UserID": "66d9c3ddc1d6fcc235ccc92e"
                        }
                    ]
                }
            ],
            "testusers": []
        }
    ]

I'd like to have the REF_UserID augmented with the user's firstName, LastName fields from the user table.

eg:

{
"REF_UserID": "66d9c3ddc1d6fcc235ccc92e",
"firstName": "Fred",
"lastName" : "Jones",
}

Answer 1

You can use $documents inside a lookup stage to perform a separate lookup into the users collection for each element in the array.

Steps in this pipeline:

• group by notifier, push user ids into an array of objects
• lookup with no collection, specifying the the array of user ids as the initial documents
• embedded lookup against the users collection to retrieve the desired data from each user record (note the duplication of the REF_UserId to simplify combining documents later)
• unwind the embedded lookup result so it is an object instead of array
• replace the original users array entry with the looked up data.

db.notifier.aggregate([
  {$group: {
      _id: "$REF_NotifierID",
      users: {$push: {REF_UserID: "$REF_UserID"}}
  }},
  {$lookup: {
      let: {docs: "$users"},
      as: "users",
      pipeline: [
        {$documents: "$$docs"},
        {$lookup: {
            from: "users",
            localField: "REF_UserID",
            foreignField: "_id",
            as: "userData",
            pipeline: [
              {$project: {
                  _id: 0,
                  firstname: 1,
                  lastname: 1,
                  REF_UserID: "$_id"
              }}
            ]
        }},
        {$unwind: "$userData"        },
        {$replaceRoot: {newRoot: "$userData"}}
      ]
  }}
])

Example: Playground