I have this structure in Mongo:
[
{
ProjectId: 111,
Billings: [
{
FieldA: 1
},
{
FieldA: 2
}
],
Extras: [
{
ExtraId: "E_111_01",
Billings: [
{
FieldA: 3
},
{
FieldA: 4
}
]
},
{
ExtraId: "E_111_02",
Billings: [
{
FieldA: 5
},
{
FieldA: 6
}
]
}
]
},
{
ProjectId: 222,
Billings: [],
Extras: [
{
ExtraId: "E_222_01",
Billings: [
{
FieldA: 7
},
{
FieldA: 8
}
]
}
]
}
]
I need to flat all 'Billings' to root, but some of them are in a first level array and other are 2 levels down.
And the process should add 2 props to 'Billing', the project ID it´s from, and if It´s an Extra, the 'Billings' should have the Project ID and the Extra ID.
How can I flat all to root? Just Unwind 2 times and MergeArray ? to push the IDs to the Billings, should I use MAP?
In this example the result would be:
[
{
ProjectId: 111,
FieldA: 1
},
{
ProjectId: 111,
FieldA: 2
}
{
ProjectId: 111,
ExtraId: "E_111_01",
FieldA: 3
},
{
ProjectId: 111,
ExtraId: "E_111_01",
FieldA: 4
}
{
ProjectId: 111,
ExtraId: "E_111_01",
FieldA: 5
},
{
ProjectId: 111,
ExtraId: "E_111_01",
FieldA: 6
}
{
ProjectId: 222,
ExtraId: "E_222_01",
FieldA: 7
},
{
ProjectId: 222,
ExtraId: "E_222_01",
FieldA: 8
}
]
Here is a Mongo Playground
cheers
Working on the assumption that FieldA in your example is a placeholder that might be multiple fields or different names, you might
Billings with the $extras arrayExtras and Billings so each document contains only oneProjectId and ExtraId to the billing objectBillings document to be the rootThis will keep any fields in each Billings document, and not require that you know the field names ahead of time.
db.collection.aggregate([
{"$project": {
_id: 0,
ProjectId: 1,
Extras: {
$concatArrays: [
[{ Billings: "$Billings" }],
"$Extras"
]
}
}},
{$unwind: "$Extras"},
{$unwind: "$Extras.Billings"},
{$addFields: {
"Extras.Billings.ExtraId": "$Extras.ExtraId",
"Extras.Billings.ProjectId": "$ProjectId"
}},
{$replaceRoot: {
newRoot: "$Extras.Billings"
}}
])
you could try this aggregation query:
db.collection.aggregate([
{
"$unwind": "$Extras"
},
{
"$addFields": {
"Extras.Billings.ExtraId": "$Extras.ExtraId"
}
},
{
"$project": {
"ProjectId": "$ProjectId",
"Billings": {
"$concatArrays": [
"$Billings",
"$Extras.Billings"
]
}
}
},
{
"$unwind": "$Billings"
},
{
"$project": {
"ProjectId": "$ProjectId",
"FieldA": "$Billings.FieldA",
"ExtraId": "$Billings.ExtraId"
}
},
{
"$group": {
"_id": {
"id": "$_id",
"FieldA": "$FieldA",
"ProjectId": "$ProjectId",
"ExtraId": "$ExtraId"
}
}
},
{
"$project": {
"_id": 0,
"FieldA": "$_id.FieldA",
"ProjectId": "$_id.ProjectId",
"ExtraIs": "$_id.ExtraId"
}
},
{
"$sort": {
"ProjectId": 1,
"FieldA": 1
}
}
])
try it online: mongoplayground.net/p/DZAIfDY5x7L