Mongo Aggregation Flat nested arrays

Question

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

user_2282634 · Answer

Working on the assumption that FieldA in your example is a placeholder that might be multiple fields or different names, you might

$project to combine the top level Billings with the $extras array
unwind Extras and Billings so each document contains only one
Add the ProjectId and ExtraId to the billing object
Promote the Billings document to be the root

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

Playground

user_6858274 · Answer

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

Mongo Aggregation Flat nested arrays

2 Answers