Pull out array of objects and use their fields out of the array (MongoDB aggregation framework)

Question

I don't know if this is possible to do, but suppose I have documents like these:

[
  {
    "L": [
      {
        "L1": 1
      }, 
      {
        "L6": 2
      }, 
      {
        "L2": 1
      }
    ], 
    "week": 26, 
    "weekEnd": "2020-06-28", 
    "weekStart": "2020-06-22"
  },
  {
    "L": [
      {
        "L5": 1
      }, 
      {
        "L2": 1
      }, 
      {
        "L3": 1
      }
    ], 
    "week": 19, 
    "weekEnd": "2020-05-10", 
    "weekStart": "2020-05-04"
  }, 
]

As you all can see, the array Lcan have different objects with fields like L1, L2, L3, ..., L30.
I would like to pull out the fields of each object out of the L array.
Expected result:

[
  {
    "L1": 1,
    "L6": 2,
    "L2": 1, 
    "week": 26, 
    "weekEnd": "2020-06-28", 
    "weekStart": "2020-06-22"
  },
  {
    "L5": 1,
    "L2": 1,
    "L3": 1, 
    "week": 19, 
    "weekEnd": "2020-05-10", 
    "weekStart": "2020-05-04"
  }
]

I could do this "from scratch", but I would have to write every field one by one in a $project stage. Is there a automatic way of doing this?

thammada.ts · Accepted Answer · 2020-07-01 16:44:39Z

1

You can use $mergeObjects (available from v3.6) to merge all keys of objects in an array. First you'll have to merge objects in array L, then you have to merge the result with the root document $$ROOT

db.collection.aggregate({
  $replaceRoot: { // 3. replace the result as root document
    newRoot: {
      $mergeObjects: [ // 2. merge with root document
        {
          $mergeObjects: "$L" // 1. merge objects in array L
        },
        "$$ROOT"
      ]
    }
  }
},
{
  $project: { // remove original L array
    L: false
  }
})

Mongo Playground

If you are using v4.2 or later you can use the following slightly shorter syntax

db.collection.aggregate({
  $replaceWith: {
    $mergeObjects: [
      {
        $mergeObjects: "$L"
      },
      "$$ROOT"
    ]
  }
},
{
  $unset: "L"
})

edited Jul 1, 2020 at 16:44

answered Jul 1, 2020 at 15:37

thammada.ts

5,2653 gold badges26 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

igorkf Over a year ago

Perfect! I used $replaceRoot due my early version of MongoDB and worked like a charm.

Collectives™ on Stack Overflow

Pull out array of objects and use their fields out of the array (MongoDB aggregation framework)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related