How to $count and $group within aggregation?

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

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