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

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

CodePudding user response：

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.