How do I write a mongoDB aggregate query for the below shown documents and get the result as shown below?

GOPAL CHOUDHARY 0
Viewed 39

I am new to mongoDB. Tried a lot of things but none worked. I have documents with fields as shown below(and many other fields, posting just relevant ones).

APP OWNER TECHNOLOGY ENVIRONMENT
appowner1 Neptune PROD
appowner2 RDS NONPROD
appowner3 DynamoDB PROD
appowner4 Redshift NONPROD
appowner5 Migration PROD
appowner6 DocumentDB NONPROD
appowner7 Elastic PROD
appowner8 Neptune NONPROD
appowner9 RDS PROD
appowner10 DynamoDB NONPROD
appowner11 Redshift PROD
appowner12 Migration NONPROD
appowner13 DocumentDB PROD
appowner14 Elastic NONPROD

How to write a mongoDB aggregate query to get the following output?

TECHNOLOGY PROD NON PROD TOTAL
Neptune 2 2 4
RDS 2 2 4
DynamoDB 2 2 4
Redshift 2 2 4
Migration 2 2 4
DocumentDB 2 2 4
Elastic 2 2 4
aggregation aggregation-framework mongodb-query mongodb
1 Answers

You can try this:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$technology",
      "PROD": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$environment",
                "PROD"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "NONPROD": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$environment",
                "NONPROD"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "TOTAL": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "technology": "$_id",
      "TOTAL": 1,
      "PROD": 1,
      "NONPROD": 1
    }
  }
])

Here, we group by technology field and calculate the PROD, NONPROD and TOTAL values. Here's the playground link.

Or as suggested in the comments by nimrod serok. You can try this, cleaner way:

db.collection.aggregate([
  {
    "$group": {
      "_id": "$technology",
      "PROD": {
        "$sum": {
          "$cond": {
            "if": {
              "$eq": [
                "$environment",
                "PROD"
              ]
            },
            "then": 1,
            "else": 0
          }
        }
      },
      "TOTAL": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "technology": "$_id",
      "TOTAL": 1,
      "PROD": 1,
      "NONPROD": {
        "$subtract": [
          "$TOTAL",
          "$PROD"
        ]
      }
    }
  }
])

Playground link.

Charchit Kapoor0
Related