CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-framework
AlexV101
AlexV101

Reputation: 25

How do I count the number of true Boolean values inside an array within a collection and group by job in MongoDB?

Given: I am given a collection that contains information for a batch.

Expectation: I need to return the number of true values for a job.

Some context: Every Job is split up into multiple batches. In the collection: The labellers contains an array where the labeller is the user and the completed says whether of not a person has completed the batch, the _id refers to the batch id, the job contains the _id of a specific job.

So I know I need to unwind and group by jobs but I just can't seem to get my query to return the correct number of true values.

The collection:

 {
    "_id": ObjectId("612370c2abbe776854d8c0ec"),
    "batch_number": 0,
    "job": ObjectId("612370c1abbe777ae8d8c0eb"),
    "labellers": [
      {
        "labeller": ObjectId("61238ed4abbe77763ed8c100"),
        "completed": true
      },
      {
        "labeller": ObjectId("61238f2babbe77c838d8c101"),
        "completed": true
      }
    ],
    "__v": 0
  },
  {
    "_id": ObjectId("612370c2abbe771a75d8c0ed"),
    "batch_number": 1,
    "job": ObjectId("612370c1abbe777ae8d8c0eb"),
    "labellers": [
      {
        "labeller": ObjectId("61238ed4abbe77763ed8c100"),
        "completed": true
      },
      {
        "labeller": ObjectId("61238f2babbe77c838d8c101"),
        "completed": false
      }
    ],
    "__v": 0
  }

Desired output

{
    "_id": ObjectId("612370c1abbe777ae8d8c0eb"),
    "count": 3
  }

What I have tried:

db.collection.aggregate({
  $unwind: "$labellers"
},
{
  $group: {
    _id: "$job",
    count: {
      $sum: {
        $cond: [
          {
            "labellers": {
              "completed": true
            }
          },
          1,
          0
        ]
      }
    }
  }
})

The output on what I have tried:

{
    "_id": ObjectId("612370c1abbe777ae8d8c0eb"),
    "count": 4
  }

Upvotes: 1

Views: 773

Answers (2)

turivishal
turivishal

Reputation: 36114

  • $filter to iterate loop of labellers.completed booleans array and filter true result
  • $size to get total elements return in above filter operation
  • $group by job and sum total elements count
db.collection.aggregate([
  {
    $group: {
      _id: "$job",
      count: {
        $sum: {
          $size: {
            $filter: {
              input: "$labellers.completed",
              cond: "$$this"
            }
          }
        }
      }
    }
  }
])

Playground

Upvotes: 1

mohammad Naimi
mohammad Naimi

Reputation: 2359

I add $match before $group and remove $cond from $group

[
  {
    '$match': {
      'job': '612370c1abbe777ae8d8c0eb'
    }
  }, {
    '$unwind': {
      'path': '$labellers'
    }
  }, {
    '$match': {
      'labellers.completed': true
    }
  }, {
    '$group': {
      '_id': '$job', 
      'count': {
        '$sum': 1
      }
    }
  }
]

Upvotes: 0

Related Questions