Aggreagation with multiple groups stage

Working with Data
1

Screenshot from 2022-09-08 18-10-50
Screenshot from 2022-09-08 18-10-50834×325 70.5 KB

HI Guys… Following is the usecase for which I have to write a query. I’m actually not able to get proper approach or Im not sure even if we can achieve. Kindly help with any possible approach for the usecase.

From the screenshot, Same OrderID is having multiple orderNumbers and their corresponding orderStatus.

The screenshot has data for only one orderID. But the actual data will have multiple orderID also.

First I want to group for each orderID. Within each orderID, I want to group by orderNumber and from each orderNumber, I want to pull the last record and generate the count for each status.

However I was able to achieve grouping with just ordeID as shown below

db.provisioningOrderInfo.aggregate([
		{$match:{$and : [{"tenantId":"XXXXXX"},{"orderDate":{$gte:"2022-08-03",$lte:"2022-08-25"}}]}},
		{$group:{
			"_id" :"$orderID",
			"lastDoc" : { "$last"  : "$$ROOT"}
		}},
		{$group:{
			"_id":null,
			"TOTAL_ORDERS" : {$sum:1},
			"SUCCESS":{"$sum": { "$cond":[ { "$eq": ["$lastDoc.orderStatus", "Success"] } , 1, 0 ] }},
			"FAILED":{"$sum": { "$cond":[ { "$eq": ["$lastDoc.orderStatus", "Failed"] } , 1, 0 ] }},
			"PARTIALLYFULFILLED":{"$sum": { "$cond":[ { "$eq": ["$lastDoc.orderStatus", "PartiallyFulfilled"] } , 1, 0 ] }},
			"PENDING":{"$sum": { "$cond":[ { "$eq": ["$lastDoc.orderStatus", "Pending"] } , 1, 0 ] }}
			}}
])

This is giving me the response properly. However, Im getting confused on introducing the second group by i.e, group by orderNumber.

Kindly help me if this kind of usecase can be tackled by aggregation query ?

2

Please provide your sample documents in textual JSON format so that we can cut-n-paste into our system.

3

In addition to the sample docs that Steeve asked for, we would need to know what the expected output should look like. This saves time with going back and forth tweaking the query.