Nested grouping in MongoDB aggregation-CodePudding

Context:

I have a MongoDB full of Documents like this, which I want to dumb into one grouped json:

[
  {
    "_id": "615dc97907f597330c510279",
    "code": "SDFSDFSDF",
    "location": "ABC1",
    "week_number": 40,
    "year": 2021,
    "region": "NA"
  },

  ....

  {
    "_id": "615dc97907f597330c51027a",
    "code": "SDFSGSGR",
    "location": "ABC1",
    "week_number": 40,
    "year": 2021,
    "region": "EU"
  },

  ....

  {
    "_id": "615dc97607f597330c50ff50",
    "code": "GGSFHSFS",
    "location": "DEF2",
    "week_number": 40,
    "year": 2021,
    "region": "EU",
    "audit_result": {
      "issues_found": true,
      "comment": "comment."
    }
  }
]

I am trying to write an aggregation which should return and object like this:

{
  [
    "EU": {
      2021: {
        40: {
          "ABC1": {
            (All documents for location ABC1 and week 40, year 2021 and region EU)
          }
        },
        39: {
          ....
        }
      },
      2020: {
        ....
      }
    },
    "NA": {
      ....
    }
  ]
}

Problem:

I am not 100% sure how.

I started grouping them by region but I am not sure how to proceed after the first group. I tried grouping them by location first and group my way up to region but that also does not seem to work as I expected it.

The docs don't talk about a case like this and examples I find only group by one or two things, not four.

any insights highly appreciated :)

CodePudding user response：

Using dynamic values as field name is generally considered as anti-pattern and you should avoid that. You are likely to introduce unnecessary difficulty to composing and maintaining your queries.

Nevertheless, you can do the followings in an aggregation pipeline:

$group at the finest level: region, year, week_number, location; $addToSet to group all the $ROOT document into an array named v
$group at 1 coarser level: region, year, week_number; create k-v tuples that k is the location and v is the v from step 1. Use $addToSet to group the k-v tuples into an array named v
use $arrayToObject to convert your k-v tuples into fields with dynamic values e.g.

"ABC" : [
    {
        "_id": "615dc97907f597330c510279", 
        ...
    }, 
    ...
]

Basically repeating step 2 & 3 at 1 coarser level: region, year; create k-v tuples that k is the location and v is the v from step 3. Use $addToSet to group the k-v tuples into an array named v
Repeat step 4 at 1 coarser level: region
$group unconditionally (i.e. $group by _id: null); repeating previous step to put the results into a single array named v; use $arrayToObject to convert it again
$replaceRoot to obtain your expected result

Here is one small note: when $arrayToObject for numeric k value like year and week_number, the k value needs to be converted into String beforehand. You can use $toString to achieve this.

Here is the Mongo playground for your reference.