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

Question

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 ?

Use $unwind before $group. { $unwind: '$response' } as it is an array. — Ashh
– Ashh, Commented Mar 11, 2020 at 11:00
Perfect. Working fine. If you can add this as an answer, I can accept. — Happy Coder
– Happy Coder, Commented Mar 11, 2020 at 11:34

Ashh · Accepted Answer · 2020-03-11 11:48:05Z

1

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 }
    }
  }
]

answered Mar 11, 2020 at 11:48

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

1 Comment

Happy Coder Over a year ago

There is a small problem. It is showing the success records also with reason as null.

Miko Chu · Accepted Answer · 2021-06-29 18:09:19Z

0

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" }
    }
  }
]

answered Jun 29, 2021 at 18:09

Miko Chu

1,42819 silver badges27 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related