How to use conditional count while doing grouping in mongodb?

Question

I need help in getting counts in the conditional grouping.

I have following JSON

[
{
    "id": "103303dd56a731e377d01f6a37badae3",
    "project_id": "10006",
    "project_name": "Project_6",
    "status": "TERM"
  },
  {
    "id": "b63826f7edd2fc3ad8449add0c04fceb",
    "project_id": "10004",
    "project_name": "Project_4",
    "status": "CMP"
  },
  {
    "id": "d46e1fcf4c07ce4a69ee07e4134bcef1",
    "project_id": "10008",
    "project_name": "Project_8",
    "status": "TERM"
  },
  {
    "id": "a9fb9e6ef40426e9add520623d521ab8",
    "project_id": "10001",
    "project_name": "Project_1",
    "status": "TERM"
  },
  {
    "id": "b63826f7edd2fc3ad8449add0c04fceb",
    "project_id": "10004",
    "project_name": "Project_4",
    "status": "QF"
  }]

So you can see I have Duplicate Projects Records. I want to get Data like this.

[
  {
    "project_id": "10007",
    "starts": 2, //Count of records where project grouped
    "Completes":3 //Where status="CMP"
    "TERMS":6 //Where status="TERM"
    "QFull":2 //Where status="QF",
    "Abandons":3 //Where status=""
  },
  {
    "project_id": "10004",
    "starts": 3, //Count of records where project grouped
    "Completes":2 //Where status="CMP"
    "TERMS":4 //Where status="TERM"
    "QFull":2 //Where status="QF",
    "Abandons":1 //Where status=""
  },
  {
    "project_id": "10001",
    "starts": 3, //Count of records where project grouped
    "Completes":2 //Where status="CMP"
    "TERMS":4 //Where status="TERM"
    "QFull":2 //Where status="QF",
    "Abandons":1 //Where status=""
  }
]

Here is the Fiddle for same: https://mongoplayground.net/p/yNerdPRjbxc

What I've tried so far:

  db.collection.aggregate([
  {
    $group: {
      _id: {
        project_id: "$project_id"
      },
      project_id: {
        $first: "$project_id"
      },
      starts: {
        $sum: 1
      }
    }
  }
])

I am not sure how can I add extra fields here based on conditions.

well... I could not see a pattern from the provided output. Can you enlighten the desired result set? Also, what do you mean by "conditional count"? — felipsmartins
– felipsmartins, Commented Jan 29, 2019 at 19:21
Sure @felipsmartins, I am trying to get data like this: project_id, no. of counts as Starts, project who have status "CMP" as completes, ... I have added the desired JSON in my question as well. and conditional count in grouping means getting the sum of a column but based on sum conditions. — Pankaj Jha
– Pankaj Jha, Commented Jan 30, 2019 at 10:01

Pankaj Jha · Accepted Answer · 2019-01-30 12:28:07Z

There is a $cond operator which can be used within $sum. So you simply add 1 if status matches your condition or 0 if it doesn't. You can try below aggregation:

db.col.aggregate([
    {
        $group: {
            _id: "$project_id",
            starts: { $sum: 1 },
            Completes: { $sum: { $cond: [ { $eq: [ "$status", "CMP" ] }, 1, 0 ] } },
            TERMS: { $sum: { $cond: [ { $eq: [ "$status", "TERM" ] }, 1, 0 ] } },
            QFull: { $sum: { $cond: [ { $eq: [ "$status", "QF" ] }, 1, 0 ] } },
            Abandons: { $sum: { $cond: [ { $eq: [ "$status", "" ] }, 1, 0 ] } },
        }
    },
    {
        $project: {
            _id: 0,
            project_id: "$_id",
            starts: 1,
            Completes: 1,
            TERMS: 1,
            QFull: 1,
            Abandons: 1
        }
    }
])

Here is a Fiddle for the same: https://mongoplayground.net/p/JOZJOhyrnRL

this fiddle contains $match as well if you want to retrieve records for specific projects

Hello @mickl, Thanks this is the exact answer I was looking for. One more thing if I may ask, let's say I have to pull records for only a few projects that are in an array like this project_ids = {10001,10005,1006} how can I group with where IN condition?
@PankajJha you can add { $match: { project_id: { $in: project_ids }}} as a first pipeline stage

Collectives™ on Stack Overflow

How to use conditional count while doing grouping in mongodb?

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related