Multiple Counts with single query in mongodb
You can try below $facet
aggregation
$count
aggregation will always give you the counts for only single matching ($match
) condition. So you need to further divide your each count into multiple section and that's what the $facet
provides by processes multiple aggregation pipelines within a single stage on the same set of input documents.
db.collection.aggregate([
{ "$facet": {
"Total": [
{ "$match" : { "ReleaseDate": { "$exists": true }}},
{ "$count": "Total" },
],
"Released": [
{ "$match" : {"ReleaseDate": { "$exists": true, "$nin": [""] }}},
{ "$count": "Released" }
],
"Unreleased": [
{ "$match" : {"ReleaseDate": { "$exists": true, "$in": [""] }}},
{ "$count": "Unreleased" }
]
}},
{ "$project": {
"Total": { "$arrayElemAt": ["$Total.Total", 0] },
"Released": { "$arrayElemAt": ["$Released.Released", 0] },
"Unreleased": { "$arrayElemAt": ["$Unreleased.Unreleased", 0] }
}}
])
Output
[{
"Total": 3,
"Released": 2,
"Unreleased": 1
}]
You can use below aggregation.
$gt > null
- to check whether field exists or not in aggregation expressions.
$cond
with $sum
to output 0 and 1 based on release date filter.
$add
to add both released and unreleased count to output total.
db.Movies.aggregate([
{"$group":{
"_id":null,
"Unreleased":{"$sum":{"$cond":[{"$and":[{"$gt":["$ReleaseDate",null]},{"$ne":["$ReleaseDate",""]}]},0,1]}},
"Released":{"$sum":{"$cond":[{"$and":[{"$gt":["$ReleaseDate",null]},{"$ne":["$ReleaseDate",""]}]},1,0]}}
}},
{"$addFields":{"Total":{"$add":["$Unreleased","$Released"]}}}
])