I have the following data in a mongodb collection:
{
"base" : "XYZ",
"zone" : "ABC WEST",
"employees" : [
{
"fullname" : "RONALD GONZALEZ",
"status" : "approved",
"days" : 1.0
},
{
"fullname" : "RODRIGO GONZALEZ",
"status" : "approved",
"days" : 4.0
},
{
"fullname" : "GUILLERMO BARROS",
"status" : "approved",
"days" : 4.0
}
]
}
{
"base" : "ABC",
"zone" : "ABC CENTER",
"employees" : [
{
"fullname" : "MARTA DIAZ CABELLO",
"status" : "approved",
"days" : 2.0
},
{
"fullname" : "ALEXIS MARAMBIO",
"status" : "approved",
"days" : 2.0
}
]
}
{
"base" : "ABC",
"zona" : "ABC AU",
"employees" : [
{
"fullname" : "JOSE MELLADO",
"status" : "approved",
"days" : 1.0
},
{
"fullname" : "MARSAL MELLADO",
"status" : "approved",
"days" : 4.0
},
{
"fullname" : "ALEJANDRO KOBBRT",
"status" : "approved",
"days" : 5.0
},
{
"fullname" : "MANUEL SOLIS",
"status" : "approved",
"days" : 4.0
}
]
}
I need sort these documents using something like (base asc, zone asc, inside employees list order by fullname asc):
{ base: 1, zone: 1, "employees.fullname" : 1 }
But with these params only can sort by base-zone, inside the employess array it's not able to sort by the name field:
I tried to get this result:
{
"_id" : ObjectId("5f5e3ab682947e5e20fa4084"),
"base" : "ABC",
"zone" : "ABC AU",
"employees" : [
{
"fullname" : "ALEJANDRO KOBBRT",
"status" : "approved",
"days" : 5.0
},
{
"fullname" : "JOSE MELLADO",
"status" : "approved",
"days" : 1.0
},
{
"fullname" : "MANUEL SOLIS",
"status" : "approved",
"days" : 4.0
},
{
"fullname" : "MARSAL MELLADO",
"status" : "approved",
"days" : 4.0
}
]
}
{
"_id" : ObjectId("5f5e3ab682947e5e20fa4083"),
"base" : "ABC",
"zone" : "ABC CENTER",
"employees" : [
{
"fullname" : "ALEXIS MARAMBIO",
"status" : "approved",
"days" : 2.0
},
{
"fullname" : "MARTA DIAZ CABELLO",
"status" : "approved",
"days" : 2.0
}
]
}
{
"_id" : ObjectId("5f5e3ab682947e5e20fa4082"),
"base" : "XYZ",
"zone" : "ABC WEST",
"employees" : [
{
"fullname" : "GUILLERMO BARROS",
"status" : "approved",
"days" : 4.0
},
{
"fullname" : "RODRIGO GONZALEZ",
"status" : "approved",
"days" : 4.0
},
{
"fullname" : "RONALD GONZALEZ",
"status" : "approved",
"days" : 1.0
}
]
}
UPDATE
Thanks for helps, following aggregate works in my case.
db.getCollection("test").aggregate(
[
{ $unwind: "$employees" }
,{ $sort: { "base": 1, "zone": 1, "employees.fullname": 1 } }
,{
$group: {
_id: { id: "$_id", base: "$base", zone: "$zone" },
employees: { $push: "$employees" }
}
}
,{
$project: {
_id: "$_id.id",
base: "$_id.base",
zone: "$_id.zone",
employees: "$employees"
}
}
,{ $sort: { "base": 1, "zone": 1 } }
]
);