Im trying to get multiple count values only from multiple documents in a collection which looks like this,( basically I want to get a count of how many are from the 4 directions)
{
"empno": 1500,
"province": "North"
}
{
"empno": 1600,
"province": "West"
}
early I found a solution and implemented following query;
([
{ "$facet": {
"N": [
{ "$match": { "province": "North" }},
{ "$count": "N" }
],
"E": [
{ "$match": { "province": "East" }},
{ "$count": "E" }
],
"S": [
{ "$match": { "province": "South" }},
{ "$count": "S" }
],
"W": [
{ "$match": { "province": "West" }},
{ "$count": "W" }
]
}},
{ "$project": {
"N": { "$arrayElemAt": ["$N.N", 0] },
"E": { "$arrayElemAt": ["$E.E", 0] },
"S": { "$arrayElemAt": ["$S.S", 0] },
"W": { "$arrayElemAt": ["$W.W", 0] },
}}
])
The output I get is
{ N: 1, W: 1 }
How can I get the values only like without the keys and also I want the blank fields that are empty to be with a 0. Like this;
{1, 0, 0, 1}
CodePudding user response：
Facet
Query
- group by null, is the thing that you needed to add to get the count
db.collection.aggregate([
{
"$facet": {
"g0": [
{
"$match": {
"province": {
"$eq": "North"
}
}
},
{
"$group": {
"_id": null,
"count": {
"$sum": 1
}
}
},
{
"$project": {
"_id": 0
}
}
],
"g1": [
{
"$match": {
"province": {
"$eq": "East"
}
}
},
{
"$group": {
"_id": null,
"count": {
"$sum": 1
}
}
},
{
"$project": {
"_id": 0
}
}
],
"g2": [
{
"$match": {
"province": {
"$eq": "South"
}
}
},
{
"$group": {
"_id": null,
"count": {
"$sum": 1
}
}
},
{
"$project": {
"_id": 0
}
}
],
"g3": [
{
"$match": {
"province": {
"$eq": "West"
}
}
},
{
"$group": {
"_id": null,
"count": {
"$sum": 1
}
}
},
{
"$project": {
"_id": 0
}
}
]
}
},
{
"$set": {
"data": {
"$map": {
"input": {
"$objectToArray": "$$ROOT"
},
"in": {
"$cond": [
{
"$eq": [
"$$d.v",
[]
]
},
0,
{
"$let": {
"vars": {
"m": {
"$arrayElemAt": [
"$$d.v",
0
]
}
},
"in": "$$m.count"
}
}
]
},
"as": "d"
}
}
}
},
{
"$project": {
"data": 1
}
}
])
Group
Query
- group is used instead of facet (facet is like 1 aggregation per field)
- each group have its index (from the array), some indexes will be missing (because no documents exist)
- add a zero-data field that has all indexes and count=0 (see bellow)
- add to zero-data, the data found (the ones that existed in the collection,and we have groups for them) the rest keep the count=0
db.collection.aggregate([
{
"$group": {
"_id": {
"$switch": {
"branches": [
{
"case": {
"$eq": [
"$province",
"North"
]
},
"then": {
"index": 0,
"province": "North"
}
},
{
"case": {
"$eq": [
"$province",
"East"
]
},
"then": {
"index": 1,
"province": "East"
}
},
{
"case": {
"$eq": [
"$province",
"South"
]
},
"then": {
"index": 2,
"province": "South"
}
},
{
"case": {
"$eq": [
"$province",
"West"
]
},
"then": {
"index": 3,
"province": "West"
}
}
],
"default": {
"index": 5
}
}
},
"count": {
"$sum": 1
}
}
},
{
"$group": {
"_id": null,
"data": {
"$push": {
"index": "$_id.index",
"province": "$province",
"count": "$count"
}
}
}
},
{
"$project": {
"_id": 0
}
},
{
"$set": {
"zero-data": [
{
"index": 0,
"count": 0
},
{
"index": 1,
"count": 0
},
{
"index": 2,
"count": 0
},
{
"index": 3,
"count": 0
}
]
}
},
{
"$set": {
"data": {
"$reduce": {
"input": "$zero-data",
"initialValue": [],
"in": {
"$let": {
"vars": {
"all_data": "$$value",
"d": "$$this"
},
"in": {
"$let": {
"vars": {
"found_data": {
"$filter": {
"input": "$data",
"cond": {
"$eq": [
"$$d.index",
"$$d1.index"
]
},
"as": "d1"
}
}
},
"in": {
"$concatArrays": [
"$$all_data",
[
{
"$cond": [
{
"$eq": [
"$$found_data",
[]
]
},
{
"index": "$$d.index",
"count": 0
},
{
"$arrayElemAt": [
"$$found_data",
0
]
}
]
}
]
]
}
}
}
}
}
}
}
}
},
{
"$project": {
"data": {
"$map": {
"input": "$data",
"in": "$$this.count"
}
}
}
}
])