MongoDB aggregate collection with group and project in nested array

Question

I have a collection in mongodb with below structure:

{ 
   "RuleSetId": "5bfd3c25fe29a81aa42a3972",
   "MachineName": "HDHX1090",
   "IsCompliant": "False",
   "InfoDate": ISODate("2019-09-15T23:14:22.032+0000")
},
{ 
   "RuleSetId": "5bfd3c25fe29a81aa42a3972",
   "MachineName": "HDHX2045",
   "IsCompliant": "True",
   "InfoDate": ISODate("2019-09-15T23:14:22.032+0000")
},
{ 
   "RuleSetId": "5bfd3c25fe29a81aa42a3972",
   "MachineName": "NDEN1295",
   "IsCompliant": "False",
   "InfoDate": ISODate("2019-09-15T23:12:22.032+0000")
},
{ 
   "RuleSetId": "5bfd3c25fe29a81aa42a3972",
   "MachineName": "HDHX1090",
   "IsCompliant": "True",
   "InfoDate": ISODate("2019-08-13T23:14:22.032+0000")
},
{ 
   "RuleSetId": "5bfd3c25fe29a81aa42a3972",
   "MachineName": "HDHX2045",
   "IsCompliant": "True",
   "InfoDate": ISODate("2019-08-13T23:14:22.032+0000")
},
{ 
   "RuleSetId": "5bfd3c25fe29a81aa42a3972",
   "MachineName": "NDEN1295",
   "IsCompliant": "True",
   "InfoDate": ISODate("2019-08-13T23:12:22.032+0000")
},
{ 
   "RuleSetId": "5bfe7ef4ed244b1d48fdaaeb",
   "MachineName": "HDHN1285",
   "IsCompliant": "False",
   "InfoDate": ISODate("2019-05-30T23:14:24.300+0000")
},
{ 
   "RuleSetId": "5bfe7ef4ed244b1d48fdaaeb",
   "MachineName": "HDHX1090",
   "IsCompliant": "False",
   "InfoDate":ISODate("2019-05-30T23:14:24.300+0000")
},
{ 
   "RuleSetId": "5bfe91b3ed244b1d48fdabcb",
   "MachineName": "HDHW2455",
   "IsCompliant": "False",
   "InfoDate": ISODate("2019-05-30T22:14:23.652+0000")
}

Description: There will be limited RuleSetId (15 to 20) and against each RuleSetId there can be n number of MachineName with IsCompliant "True" or "False" on an individual InfoDate, Same can be repeated on different InfoDate.

Now i want to group on "RuleSetId", "MachineName" and "InfoDate" column and apply "count" based on "IsCompliant" value and I want resultant output should look like below:

{ 
   "RuleSetId":"5bfd3c25fe29a81aa42a3972",
   "History":[ 
      { 
         "InfoDate":"2019-09-15",
         "CompliantCount":1,
         "NonCompliantCount":2
      },
      { 
         "InfoDate":"2019-08-13",
         "CompliantCount":3,
         "NonCompliantCount":0
      }
   ]
},
{ 
   "RuleSetId":"5bfe7ef4ed244b1d48fdaaeb",
   "History":[ 
      { 
         "InfoDate":"2019-05-30",
         "CompliantCount":0,
         "NonCompliantCount":3
      }
   ]
}

Is it possible to accomplish this using the aggregated methods of mongodb or you will suggest some different approach to accomplish it?

Thanks in advance, Rajiv

Ashh · Accepted Answer · 2019-09-18 09:57:01Z

1

You can use below aggregation

db.collection.aggregate([
  { "$group": {
    "_id": {
      "RuleSetId": "$RuleSetId",
      "InfoDate": { "$dateToString": { "date": "$InfoDate", "format": "%d:%m:%Y" }}
    },
    "CompliantCount": {
      "$sum": { "$cond": [{ "$eq": ["$IsCompliant", "True"] }, 1, 0] }
    },
    "NonCompliantCount": {
      "$sum": { "$cond": [{ "$eq": ["$IsCompliant", "True"] }, 0, 1] }
    }
  }},
  { "$group": {
    "_id": "$_id.RuleSetId",
    "History": {
      "$push": {
        "InfoDate": "$_id.infoDate",
        "NonCompliantCount": "$NonCompliantCount",
        "CompliantCount": "$CompliantCount"
      }
    }
  }}
])

answered Sep 18, 2019 at 9:57

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MongoDB aggregate collection with group and project in nested array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related