How to group and count based on a field value

user_18880928 0
Viewed 30

I need to group the information and put sub items inside that group, containing the repeated values.

Collection

[
  {
    "Name": "Weight",
    "Status": "OK"
  },
  {
    "Name": "Weight",
    "Status": "OK"
  },
  {
    "Name": "Weight",
    "Status": "NOT OK"
  },
  {
    "Name": "Weight",
    "Status": "EMPTY"
  },
  {
    "Name": "Weight",
    "Status": "DELETED"
  },
  {
    "Name": "Height",
    "Status": "OK"
  },
  {
    "Name": "Height",
    "Status": "NOT OK"
  },
  {
    "Name": "Height",
    "Status": "NOT OK"
  },
  {
    "Name": "Height",
    "Status": "EMPTY"
  },
  {
    "Name": "Length",
    "Status": "OK"
  },
  {
    "Name": "Length",
    "Status": "NOT OK"
  },
  {
    "Name": "Length",
    "Status": "EMPTY"
  },
  {
    "Name": "Length",
    "Status": "EMPTY"
  }
]

Expected result

  "Weight": {
    "OK": 2,
    "NOT OK": 1,
    "EMPTY": 1,
    "DELETED": 1
  }

  "Height": {
    "OK": 1,
    "NOT OK": 2,
    "EMPTY": 1,
    "DELETED": 0
  }

  "Length": {
    "OK": 1,
    "NOT OK": 1,
    "EMPTY": 2,
    "DELETED": 0
  }
aggregation-framework mongodb
1 Answers

Query

  • group by name and count the OK,NOT OK,EMPTY,DELETED we count the 4 types of status seperatly, checking the status type
  • create the object with the structure you need (you have data on keys, so code is a bit more complex and $arrayToObject is used)
  • replace the root with it
  • group to combine them all in 1 final document(maybe you dont need this)
  • replace the root with it

*query is made assuming that the status can have only 1 of those 4 values, but i guess this is your case

Playmongo

aggregate(
[{"$group": 
   {"_id": "$Name",
    "OK": {"$sum": {"$cond": [{"$eq": ["$Status", "OK"]}, 1, 0]}},
    "NOT OK": {"$sum": {"$cond": [{"$eq": ["$Status", "NOT OK"]}, 1, 0]}},
    "EMPTY": {"$sum": {"$cond": [{"$eq": ["$Status", "EMPTY"]}, 1, 0]}},
    "DELETED": 
     {"$sum": {"$cond": [{"$eq": ["$Status", "DELETED"]}, 1, 0]}}}},
 {"$replaceRoot": 
   {"newRoot": 
     {"$arrayToObject": 
       [[{"k": "$_id",
            "v": 
             {"OK": "$OK",
              "NOT OK": "$NOT OK",
              "EMPTY": "$EMPTY",
              "DELETED": "$DELETED"}}]]}}},
 {"$group": {"_id": null, "docs": {"$mergeObjects": "$$ROOT"}}},
 {"$replaceRoot": {"newRoot": "$docs"}}])
user_48826920
Related