MongoDB Aggregation: Add each array value to each object inside array from another field. Merge/Combine two arrays from different fields

Question

I am in the middle of complex aggregation query (with lookup and many group/unwind stages) and encounter problem with combining two different arrays from different fields.

As for now, after one of the stages (in the middle or after lookup of my big query) I have the following output:

{
  _id: 1,
  quantity: [2, 1, 3]
  reagents: [ 
     {name: "FirstItem", other_field: ... },
     {name: "SecondItem", other_field: ... },
     {name: "ThirdItem", other_field: ... }
  ]
}

And I want:

  _id: 1,
  reagents: [ 
     {name: "FirstItem", quantity: 2 },
     {name: "SecondItem", quantity: 1 },
     {name: "ThirdItem", quantity: 3 }
  ]

So each [quantity] value I want to add to every object inside [reagents] as field according to it's index, like first {object} inside reagents should have first element from [quantity]

Schema with array indexes):

quantity: [2, 1, 3, ... n]
           ↓  ↓  ↓      ↓
reagents: [1, 2, 3, ... n]

THE PROBLEM:

I cannot do it after or before aggregation because:

I don't have a necessary data, cause it's result of lookup from different collections.
I cannot do it after because right after this, I should $group data by another field, so [reagents] sorting order have been lost and cannot be restored.

UPD: I understand that I could (and probably should use $unwind stage for [reagents] but I haven't found any relevant Aggregation Stage Operators in Mongo Manual. So if you know a necessary one, please point me in.

Do you need to keep other fields in reagents?

matthPen
– matthPen

2020-04-13 10:41:35 +00:00
Commented Apr 13, 2020 at 10:41 — matthPen
– matthPen, Commented Apr 13, 2020 at 10:41

Ashh · Accepted Answer · 2020-04-13 10:34:24Z

3

You can add below stage

 { "$addFields": {
   "reagents": {
     "$map": {
       "input": { "$range": [0, { "$size": "$reagents" }] },
       "in": {
         "name": {
           "$arrayElemAt": ["$reagents.name", "$$this"]
         },
         "quantity": {
           "$arrayElemAt": ["$quantity", "$$this"]
         }
       }
     }
   }
 }}

MongoPlayground

answered Apr 13, 2020 at 10:34

Ashh

46.6k16 gold badges111 silver badges137 bronze badges

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

1 Comment

AlexZeDim Over a year ago

Just "wow". I have heard something about MapReduce, but I have never would thought that $addFields with map could be my solution. I'll dig to the botton of it.

matthPen · Accepted Answer · 2020-04-13 10:53:24Z

3

@Ashh's answer will work perfectly (and afaik is the best solution) if you don't need to keep other fields than name and quantity in your results. But if you have to keep them, you'll have to add each field manually, what can be sometimes tedious. There's another 'general' solution to resolve this case, with $zip operator which is designed to merge arrays by index.

db.collection.aggregate([
  {
    $addFields: {
      reagents: {
        $map: {
          input: {
            $zip: {
              inputs: [
                "$quantity",
                "$reagents"
              ]
            }
          },
          as: "reagent",
          in: {
            $mergeObjects: [
              {
                $arrayElemAt: [
                  "$$reagent",
                  1
                ]
              },
              {
                quantity: {
                  $arrayElemAt: [
                    "$$reagent",
                    0
                  ]
                }
              }
            ]
          }
        }
      }
    }
  }
])

Mongo playground

answered Apr 13, 2020 at 10:53

matthPen

4,3731 gold badge18 silver badges18 bronze badges

1 Comment

AlexZeDim Over a year ago

Yea, thank you so much. You answer is also relevant, because in my case [reagents] also have other fields too.

Collectives™ on Stack Overflow

MongoDB Aggregation: Add each array value to each object inside array from another field. Merge/Combine two arrays from different fields

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related