Nested grouping in MongoDB aggregation

Question

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 :)

Answer 1

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:

1. $group at the finest level: region, year, week_number, location; $addToSet to group all the $ROOT document into an array named v
2. $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
3. use $arrayToObject to convert your k-v tuples into fields with dynamic values e.g.

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

4. 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
5. Repeat step 4 at 1 coarser level: region
6. $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
7. $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.

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        region: "$region",
        year: "$year",
        week_number: "$week_number",
        location: "$location"
      },
      "v": {
        "$addToSet": "$$ROOT"
      }
    }
  },
  {
    $group: {
      _id: {
        region: "$_id.region",
        year: "$_id.year",
        week_number: "$_id.week_number"
      },
      v: {
        "$addToSet": {
          k: "$_id.location",
          v: "$v"
        }
      }
    }
  },
  {
    "$addFields": {
      "v": {
        "$arrayToObject": "$v"
      }
    }
  },
  {
    $group: {
      _id: {
        region: "$_id.region",
        year: "$_id.year"
      },
      v: {
        "$addToSet": {
          k: {
            "$toString": "$_id.week_number"
          },
          v: "$v"
        }
      }
    }
  },
  {
    "$addFields": {
      "v": {
        "$arrayToObject": "$v"
      }
    }
  },
  {
    $group: {
      _id: {
        region: "$_id.region"
      },
      v: {
        "$addToSet": {
          k: {
            "$toString": "$_id.year"
          },
          v: "$v"
        }
      }
    }
  },
  {
    "$addFields": {
      "v": {
        "$arrayToObject": "$v"
      }
    }
  },
  {
    $group: {
      _id: null,
      v: {
        "$addToSet": {
          k: "$_id.region",
          v: "$v"
        }
      }
    }
  },
  {
    "$addFields": {
      "v": {
        "$arrayToObject": "$v"
      }
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$v"
    }
  }
])

Here is the Mongo playground for your reference.