I am new to Mongo Db and would appreciate some help with this query. I have been sifting through posts here for the past couple of days tearing my hair to see if I could find anything related to my query but with no luck.
I have a collection with documents similar in structure to below :
_id: xyz Movieid: 123 MovieName: Titanic ReleaseDate: 2000-01-01 _id: uvw Movieid: 456 MovieName: Titanic II ReleaseDate: 2018-01-01 _id: pqr Movieid: 789 MovieName: Titanic III ReleaseDate: I would like to achieve the output as counts for totalmovies, movies with release date, and movies without release date in 3 seperate columns as below:
Total | Released | UnReleased 3 | 2 | 1 I was able to write individual queries to execute the counts, but I am unable to successfully consolidate all that into a single query. The end goal is to create one view producing these counts as output. I have tried using operators such as $and, but can't seem to get the query to work as desired....this is as far as I got :
db.getCollection("Movies").aggregate({ "$and": [ { "$match": { "ReleaseDate": { "$exists": true } }}, { "$count": "Total" }, { "$match": { "ReleaseDate": { "$exists": true, "$nin": [""] } }}, { "$count": "Released" }, { "$match": { "ReleaseDate": { "$exists": true, "$in": [""] } }}, { "$count": "Unreleased" } ] }) 3 Answers
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] } }} ]) [{ "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"]}}} ]) db.Movies.aggregate( // Pipeline [ // Stage 1 { $group: { _id: null, Total: { $sum: 1 }, docs: { $push: '$$ROOT' } } }, // Stage 2 { $project: { _id: 0, Total: 1, Released: { $filter: { input: "$docs", as: "doc", cond: { $ne: ["$$doc.ReleaseDate", ""] } } }, Unreleased: { $filter: { input: "$docs", as: "doc", cond: { $eq: ["$$doc.ReleaseDate", ""] } } }, } }, // Stage 3 { $project: { Total: 1, Released: { $size: '$Released' }, UnReleased: { $size: '$Unreleased' } } }, ] ); 
