Mongodb update pipeline update a field of a element inside a nested array

Question

I have a collection that contains objects such as this.

{
  materials: {
    "m1": {
      inventory: [
        {
          price: 100,
          amount: 65
        }
      ]
    }
  }
}

As you can see inventory is an array deep inside the hierarchy. I want to update the amount field of it.

From the client I receive the material id ("m1") and the inventory index (0).

I have to use an update pipeline because I am setting and unsetting some other fields in this document.

This is what I tried:

await products.findOneAndUpdate(filters, [
  {
    $set: {
      "materials.m1.inventory.0.amount": 100,
    },
  },
]);

But it creates a new field named 0 inside the 0th element and sets the amount inside that object. So the resulting document looks like this.

{
  materials: {
    "m1": {
      inventory: [
        {
          0: {
            amount: 100
          }
          price: 100,
          amount: 65
        }
      ]
    }
  }
}

Whereas what I want is this:

{
  materials: {
    "m1": {
      inventory: [
        {
          price: 100,
          amount: 100
        }
      ]
    }
  }
}

The only way for me to identify which element in the array to update is the index of it.

I am using nodejs mongodb driver. How to write the $set stage for this update pipeline?

turivishal · Accepted Answer · 2021-08-25T15:45:16.163

5

I don't think any other straightway to update the specific index position element using aggregation pipeline,

$range to generate array from 0 to length of materials.m1.inventory array
$map to iterate loop of above range array
$cond to check current element is 0 then go to update part otherwise return element from materials.m1.inventory by input index
$arrayElemAt to get specific element from materials.m1.inventory
$mergeObjects to merge current object with amount updated property

await products.findOneAndUpdate(filters, [
  {
    $set: {
      "materials.m1.inventory": {
        $map: {
          input: {
            $range: [0, { $size: "$materials.m1.inventory" }]
          },
          in: {
            $cond: [
              { $eq: ["$$this", 0] }, // 0 position
              {
                $mergeObjects: [
                  { $arrayElemAt: ["$materials.m1.inventory", "$$this"] },
                  { amount: 100 } // input amount
                ]
              },
              { $arrayElemAt: ["$materials.m1.inventory", "$$this"] }
            ]
          }
        }
      }
    }
  }
]);

Playground

edited Aug 25 '21 at 15:45

answered Aug 25 '21 at 14:31

turivishal

34,368
7
36
59

2

Very good solution. That `$mergeObjects` came in handy here. – Marco Luzzara Aug 25 '21 at 15:58
I don't think there is need to iterate you could just use $arrayElemAt and $mergeObjects to prepare the updated element followed by $concatArrays and $slice to join the array as noted in the other answer - not sure which is efficient solution – s7vr Aug 26 '21 at 02:00
1

@s7vr you are right, we are not sure but if OP wants to update any other position instead of 0th, then he can by my solution. the other answer is a very good solution only for 0th position update. – turivishal Aug 26 '21 at 03:51
1

I do want to update other indexes. And also sometimes multiple indexes of the same array. This works best for me. Thanks a lot! – Aruna Herath Aug 28 '21 at 00:46

Marco Luzzara · Answer 2 · 2021-08-25T16:03:46.293

The problem with findOneAndUpdate using an aggregation pipeline is that you can only use $set, $unset and $replaceWith (with their aliases). It seems like that $set, or $addFields, is not capable of overwriting objects inside arrays, so you can simply re-create the whole array with the changes you need. You are basically forced to build your query like this:

db.collection.update(filters,
[
  {
    "$addFields": {
      "materials.m1.inventory": {
      ...
      }
    }
  }
])

This is my implementation but there can be more straightforward ways (See edit that replaces the single-object array):

db.collection.update(filters,
[
  {
    "$addFields": {
      "materials.m1.inventory": {
        $concatArrays: [
          [
            {
              price: {
                $getField: {
                  field: "price",
                  input: {
                    $first: "$materials.m1.inventory"
                  }
                }
              },
              amount: 100
            }
          ],
          {
            $slice: [
              "$materials.m1.inventory",
              1,
              {
                $size: "$materials.m1.inventory"
              }
            ]
          }
        ]
      }
    }
  }
])

I am using $concatArrays to concat:

An array with a single object, which is the one at index 0 but with the amount field set to 100
The slice of the inventory array starting from index 1.

Obviously, if the index was different than 0, you would have to take a first slice inventory[0:index], a single-object array (see point 1), and the slice inventory[index + 1:].

Here the demo.

EDIT: The $mergeObjects part proposed by @turivishal is definitely better than my single-object array: whether you have 2 or 20 fields the code is the same.

Thanks. This helped me to understand mongodb pipelines better. I guess the updating array indexes doesn't work because the syntax I expected to work is ambiguous on whether to update 0th element or add a new field called 0. Yeah the other answer is easier to use as I have to update other indexes and sometimes multiple indexes of the same array. — Aruna Herath, Aug 28 '21 at 00:49
As I said you can always do 2 slices and take the object in the middle index. I do not know which solution is better in terms of performances, but the accepted one is certainly easy to understand :) — Marco Luzzara, Aug 28 '21 at 07:38

Mongodb update pipeline update a field of a element inside a nested array

2 Answers2