CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Happy Coder
Happy Coder

Reputation: 4692

Group by count over a collection of nested json array documents in MongoDB

I have data in mongo collection in the following format:

{
  "response":[
      {
          "_responsedata":{
              "Error":{
                  "message":"BMERROR001 - Something went wrong. Please check the error logs",
                  "statusCode":"400",
                  "reasonCode":"BMERROR001"
              }
          },
          "_responsemeta":{
              "status":"400"
          }
      },
      {
          "_responsedata":{
              "Error":{
                  "message":"BMERROR001 - Something went wrong. Please check the error logs",
                  "statusCode":"400",
                  "reasonCode":"BMERROR001"
              }
          },
          "_responsemeta":{
              "status":"400"
          }
      },
      {
          "_responsedata":{
              "Error":{
                  "message":"BMERROR002 - Something went wrong. Please check the error logs",
                  "statusCode":"400",
                  "reasonCode":"BMERROR002"
              }
          },
          "_responsemeta":{
              "status":"400"
          }
      },
      {
          "_responsedata":{
              "name":"name1",
              "col1":"value1"
          },
          "_responsemeta":{
              "status":"204"
          }
      },
      {
          "_responsedata":{
              "name":"name2",
              "col1:":"value2"
          },
          "_responsemeta":{
              "status":"201"
          }
      },
      {
          "_responsedata":{
              "Error":{
                  "message":"BMERROR003 - Something went wrong. Please check the error logs",
                  "statusCode":"400",
                  "reasonCode":"BMERROR003"
              }
          },
          "_responsemeta":{
              "status":"400"
          }
      }
  ]
}

This ihe format of one documents and I have many documents in the same format. Now for making this documents in a format, which can be visualized using D3, I need to group them based on the reasonCode field. So I need something like this as a result :

{
    "errors": [
        {
            "code":"BMERROR001",
            "count":2
        },
        {
            "code":"BMERROR002"
            "count":"1"
        },
         {
            "code":"BMERROR003"
            "count":"1"
        }
     ]
}

I tried with the following aggregation, but it is giving the reason as an array and the count.

[{$group: {
  _id:{
    "reason":"$response._responsedata.Error.reasonCode"
  },
  count:{$sum:1}
}}]

How can I take the count grouped by the reasonCode properly ?

Upvotes: 2

Views: 766

Answers (2)

Miko Chu
Miko Chu

Reputation: 1392

For those having a deep-nested arrays, you can do it like so:

{
  "pets": [
    "dogs": [
      "name": "douge",
      "quantity": 5
    ]
  ]
}

query

[
  { $unwind: "$pets" },
  {
    $unwind: {
     path: "$pets.dogs",
     preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
     _id: null,
     sum: { $sum: "$pets.dogs.quantity" }
    }
  }
]

Upvotes: 0

Ashh
Ashh

Reputation: 46481

You need to $unwind the response array first then you can apply $group on nested reasonCode.

[
  { $unwind: "$response" },
  {
    $group: {
      _id: {
        reason: "$response._responsedata.Error.reasonCode"
      },
      count: { $sum: 1 }
    }
  }
]

Upvotes: 1

Related Questions