Hi all,
this time I am trying to do two steps in the aggregation, but can’t wrap my head around it.
I would like to count the status
and group them by country
.
orders: [
{ id: 100, status: 'ordered', country: 'US', items: [] },
{ id: 101, status: 'ordered', country: 'UK', items: [] },
{ id: 102, status: 'shipped', country: 'UK', items: [] },
]
Desired aggregation outcome:
ag: [
{ _id: 'US', status: { ordered: 1} },
{ _id: 'UK', status: { ordered: 1, shipped: 1 } }
]
I can $count
and $group
, but I am not sure how to put this together…
Thanks,
bluepuama
steevej
(Steeve Juneau)
2
The hard part is that you want values (ordered and shipped) to become field names. I tried but the closest I can get is
{ "_id" : "US", "counts" : [ { "status" : "ordered", "count" : 1 } ] }
{ "_id" : "UK", "counts" : [ { "status" : "shipped", "count" : 1 }, { "status" : "ordered", "count" : 1 } ] }
with the following:
[
{
"$group" : {
"_id" : {
"country" : "$country",
"status" : "$status"
},
"count" : {
"$sum" : 1
}
}
},
{
"$group" : {
"_id" : "$_id.country",
"counts" : {
"$push" : {
"status" : "$_id.status",
"count" : "$count"
}
}
}
}
]
This is as far I can go. I am pretty sure, $function could do the rest but I am not familiar enough.
1 Like