I have the following objects in my MongoDB
[
{
'status': 'SENT',
'country': '_bg',
'createdAt': '<dataTime>',
},{
'status': 'CREATED',
'country': '_de',
'createdAt': '<dataTime>',
},{
'status': 'SENT',
'country': '_de',
'createdAt': '<dataTime>',
},{
'status': 'ERROR',
'country': '_de',
'createdAt': '<dataTime>',
},{
'status': 'SENT',
'country': '_bg',
'createdAt': '<dataTime>',
},
]
and now I'm trying to write an aggregate query to get the desired output
[
{
'_de': {
'SENT': 1,
'CREATED': 1,
'ERROR': 1,
'WAITING': 0
},
'_bg': {
'SENT': 2,
'CREATED': 0,
'ERROR': 0,
'WAITING': 0
}
}
]
How to write a query to group by per "countries" and then perform another group by (within the current group by) to count the number of different statuses? Also, if there are no results per status I need to output 0. All available statuses are "SENT", "ERROR", "WAITING", "CANCELED".
Currently I'm working with this query, but it doesn't output wishful results.
const totalPerCounty = await Model.aggregate([
{
$match: {
createdAt: {
$gt: new Date(from),
$lt: new Date(to),
}
},
},
{
$group: {
_id: {country: '$country'},
status: {
$push: {
status: "$status",
count: "$count"
}
},
'count': {$sum: 1}
}
}
]);
If you need any additional explanations, please let me know and I will provide an answer. Thank you!
CodePudding user response:
$match
$group
- Group bycountry
. And pushstatus
intostatus
array.$group
- Group by null. This aims to combine all the documents into one by push the$$ROOT
document intodata
array.$replaceRoot
- Replace input documents.4.1.
$arrayToObject
- Convert the array into key-value pair.4.1.1.
$map
- Iteratedata
array element(s) and return a new array with the document containsk
as country andv
as set of status key-value pair.4.1.1.1.
$arrayToObject
- Convert the array into key-value pair.4.1.1.1.1.
$map
- Iterate the status array and return a new array with the document containsk
ass status andv
is the count of matching status fromdata.status
array.
db.collection.aggregate([
{
$match: {
createdAt: {
$gt: new Date(from),
$lt: new Date(to),
}
},
},
{
$group: {
_id: "$country",
status: {
$push: "$status"
}
}
},
{
$group: {
_id: null,
data: {
$push: "$$ROOT"
}
}
},
{
$replaceRoot: {
newRoot: {
$arrayToObject: {
$map: {
input: "$data",
as: "data",
in: {
k: "$$data._id",
v: {
$arrayToObject: {
$map: {
input: [
"SENT",
"ERROR",
"WAITING",
"CANCELED"
],
as: "status",
in: {
k: "$$status",
v: {
$size: {
$filter: {
input: "$$data.status",
cond: {
$eq: [
"$$this",
"$$status"
]
}
}
}
}
}
}
}
}
}
}
}
}
}
}
])