MongoDB Aggregate Group Results Query by Common Fields

Looking to figure out how to perform a particular grouping of Mongo documents through a query, instead of transforming the results through code after getting the data.

Sample mock data:

[
    { 'id': 1, 'status': 'EMPTY', 'name': 'ALBERT' },
    { 'id': 2, 'status': 'EMPTY', 'name': 'ERIC' },
    { 'id': 3, 'status': 'EMPTY', 'name': 'ALBERT' },
    { 'id': 4, 'status': 'EMPTY', 'name': 'ALBERT' },
    { 'id': 5, 'status': 'EMPTY', 'name': 'JESSICA' },
    { 'id': 6, 'status': 'EMPTY', 'name': 'ERIC' },
    ...
]

My aggregate query:

db.results.aggregate([
    { 
        $match: {
            'status': 'EMPTY'
        }
    },
    ???
])

I would like to transform/sort the results into an object like this:

{
    'ALBERT': [
        { 'id': 1, 'status': 'EMPTY', 'name': 'ALBERT' },
        { 'id': 3, 'status': 'EMPTY', 'name': 'ALBERT' },
        { 'id': 4, 'status': 'EMPTY', 'name': 'ALBERT' },
    ],
    'ERIC': [
        { 'id': 2, 'status': 'EMPTY', 'name': 'ERIC' },
        { 'id': 6, 'status': 'EMPTY', 'name': 'ERIC' }
    ],
    'JESSICA': [
        { 'id': 5, 'status': 'EMPTY', 'name': 'JESSICA' }
    ]
}

It's a bit difficult looking through documentation online since I'm not even sure what this type of transformation is called, or what aggregator I should be using in Mongo. So any point in the right direction would be much appreciated!

Thank you!!


Try this one:

db.collection.aggregate([
  { $unset: "_id" },
  {
    $group: {
      _id: "$name",
      data: { $push: "$$ROOT" }
    }
  },
  {
    $project: {
      data: {
        k: "$_id",
        v: "$data"
      }
    }
  },
  { $replaceRoot: { newRoot: { $arrayToObject: "$data" } } },
  {
    $group: {
      _id: null,
      data: { $push: "$$ROOT" }
    }
  },
  { $replaceRoot: { newRoot: { $mergeObjects: "$data" } } }
])

Mongo Playground