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 :)
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
$arrayToObject
to convert your k-v tuples into fields with dynamic values e.g."ABC" : [
{
"_id": "615dc97907f597330c510279",
...
},
...
]
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
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 resultHere 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.