Home > Back-end >  MongoDB: Count total number of true and false values for documents matching a query
MongoDB: Count total number of true and false values for documents matching a query

Time:11-22

My question is exactly same to this

MongoDB Count total number of true and false values for documents matching a query

I followed the same solution but I am not getting the desired output.

I have some products records in database. Some of them are free and some are not. I want to retrieve the count of free and non-free products. This is my records

{ "_id" : ObjectId("54abcdbeba070410146d6073"), "name" : "Product 1", "isFree" : true}
{ "_id" : ObjectId("54afe32fec4444481b985711"), "name" : "Product 2", "isFree" : false}
{ "_id" : ObjectId("54b66de68dde7a0c19be987b"), "name" : "Product 3", "isFree" : false}
{ "_id" : ObjectId("54b66de68dde7a0c19bc897d"), "name" : "Product 4", "isFree" : false}

I am expecting output as:

{
   "free": 1,
   "nonFree": 3
}

This is my query:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$isFree",
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$group": {
      _id: null,
      free: {
        $sum: {
          $cond: ["_id",1,0]
        }
      },
      nonFree: {
        $sum: {
          $cond: ["_id",0,1]
        }
      }
    }
  }
])

I am getting output as:

{
    "_id": null,
    "free": 2,
    "nonFree": 0
}

CodePudding user response:

You can skip the first group, and use $cond to evaluate isFree directly:

db.collection.aggregate([
  {
    "$group": {
      _id: null,
      free: {
        $sum: {
          $cond: ["$isFree",1,0]
        }
      },
      nonFree: {
        $sum: {
          $cond: ["$isFree",0,1]
        }
      }
    }
  },
  {$project:{_id:0}}
])

Playground

  • Related