Question

0

203

Views

Mongo Aggregation Flat nested arrays

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

over 4 years ago · Santiago Trujillo

2 answers

Answer question

0

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

over 4 years ago · Santiago Trujillo Report

Answer question

Find remote jobs

Accepted Answer · 2022-05-13T14:37:55.929Z

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

0

203

Mongo Aggregation Flat nested arrays

2 answers

0

0

Find remote jobs

Andres GPT