CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Pankaj Jha
Pankaj Jha

Reputation: 886

How to use conditional count while doing grouping in mongodb?

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.

Upvotes: 2

Views: 76

Answers (1)

mickl
mickl

Reputation: 49985

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

Upvotes: 2

Related Questions