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