How to make a sum only query in a whole collection?

user_1114043 0
Viewed 34

I have this collection

{ 
    "_id" : BinData(0, "JUw6VoBVdtqAQ2g7sn0sog=="), 
    "os_name" : "android" 
}

I want to retrieve in one aggregate query to count of documents with

os_android => count of documents where "os_name" = "android" 
os_ios => count of documents where "os_name" = "ios"
total => the total count of document

How can i do ?

I try this :

db.getCollection("myCollection").aggregate(
[
    {$group:{
      _id:{},
      by_os_ios:{$sum:{$cond:[{os_name:"ios"},NumberInt(1),NumberInt(0)]}},
      by_os_android:{$sum:{$cond:[{os_name:"android"},NumberInt(1),NumberInt(0)]}},
      total:{$sum:1}}
    },
    {$addFields:{
      "by_os.ios":"$by_os_ios",
      "by_os.android":"$by_os_android"}},
    {$project:{
      _id:0,
      by_os_ios:0,
      by_os_android:0}}
  ]
);

but it's not work :( what did I miss ?

mongodb-query mongodb
1 Answers

You missed $eq in $cond, you can check condition using { $eq:["$field_name", "value to matched"] }

db.getCollection("myCollection").aggregate([
  {
    $group: {
      _id: {},
      by_os_ios: { $sum: { $cond: [{ $eq: ["$os_name", "ios"] }, 1, 0] } },
      by_os_android: { $sum: { $cond: [{ $eq: ["$os_name", "android"] }, 1, 0] } },
      total: { $sum: 1 }
    }
  },
  ... // your other pipeline stages
])

Playground

Second possible way,

  • $group to create array called root
  • $project to get count of total documents using $size
  • $reduce to iterate loop through root array and check condition if its ios then sum and if its android then sum using $add and merge with current object of value by $mergeObjects

Playground

user_89871280
Related