I need to create a query using mongoose, which allows me to use the group, to count the elements that are in the STARTED or NOT INITIATED state, and then return the count of the elements that are in each of the states.
This is the json object that I am working with.
[
{
status: "INITIATED",
_id: "6057e0013a3dec1d44a7d152",
group: "dairy",
location: "001",
total_items: 30,
__v: 0
},
{
status: "INITIATED",
_id: "6057e0013a3dec1d44a7d153",
group: "dairy",
location: "002",
total_items: 45,
__v: 0
},
{
status: "INITIATED",
_id: "6057e0013a3dec1d44a7d154",
group: "dairy",
location: "003",
total_items: 12,
__v: 0
},
{
status: "NOT INITIATED",
_id: "6057e0013a3dec1d44a7d155",
group: "dairy",
location: "004",
total_items: 50,
__v: 0
},
{
status: "NOT INITIATED",
_id: "6057e0013a3dec1d44a7d156",
group: "drugs",
location: "005",
total_items: 23,
__v: 0
},
{
status: "NOT INITIATED",
_id: "6057e0013a3dec1d44a7d157",
group: "drugs",
location: "006",
total_items: 76,
__v: 0
}]
For example, from the dairy group I need to return a json with the following structure.
{
'id': null,
'group': 'dairy',
'INITIATED': 3,
'NO INITIATED': 1
}
Because of the dairy group 3 of the elements are in the STARTED state and one of the elements is in the NOT INITIATED state. Another example would be with the group of drugs that should return the following json.
{
'id': null,
'group': 'medications',
'STARTED': 0,
'NOT INITIATED': 2
}
In order to do this I am trying to get the following code to work.
db.collection.aggregate ([
{
"$ group": {
_id: {
source: "$ group",
status: "$ status"
},
count: {
$ sum: 1
}
}
}
])
But I am not very clear on how to use the group to generate the response as I need it.
Ok, so this was trickier than I thought at first, but here's how you could do it:
First, you group your documents by the group field, and sum up the counts for the status field. I'm using the $cond and $eq operators to make sure that the status matches the count field. Finally, you apply $project to get the desired output without the _id field:
db.collection.aggregate([
{ "$group": {
"_id": "$group",
"NOT INITIATED": {
"$sum": {
"$cond": [ { "$eq": [ "$status", "NOT INITIATED" ] }, 1, 0]
}
},
"INITIATED": {
"$sum": {
"$cond": [ { "$eq": [ "$status", "INITIATED" ] }, 1, 0]
}
},
} },
{ "$project": {
"_id": 0,
"group": "$_id",
"INITIATED":1,
"NOT INITIATED":1
} }])
I've created an example on mongoplayground for you: https://mongoplayground.net/p/H-72VzWoeoN