2

I have a collection full of products each of which has a subdocument array of up to 100 variants (SKUs) of that product:

e.g.

{
    '_id': 12345678,
    'handle': 'my-product-handle',
    'updated': false
    'variants': [
         {
              '_id': 123412341234,
              'sku': 'abc123',
              'inventory': 1
         },
         {
              '_id': 123412341235,
              'sku': 'abc124',
              'inventory': 2
         },
         ...
    ]
 }

My goal is to be able to update the inventory quantity of all instances of a SKU number. It is important to note that in the system I'm working with, SKUs are not unique. Therefore, if a SKU shows up multiple times in a single product or across multiple products, they all need to be updated to the new inventory quantity.

Furthermore, I need the "updated" field to be changed to "true" *only if the inventory quantity for that SKU has changed"

As an example, if I want to update all instances of SKU "abc123" to have 25 inventory, the example of above would return this:

{
    '_id': 12345678,
    'handle': 'my-product-handle',
    'updated': true
    'variants': [
         {
              '_id': 123412341234,
              'sku': 'abc123',
              'inventory': 25
         },
         {
              '_id': 123412341235,
              'sku': 'abc124',
              'inventory': 2
         },
         ...
    ]
 }

Thoughts?

ChrisC
  • 916
  • 1
  • 10
  • 24
  • the old question has multiple answers indicating this cannot be done, however last year this limitation was removed. – Asya Kamsky Aug 09 '18 at 22:34

1 Answers1

4

MongoDB 3.6 has introduced the filtered positional operator $[<identifier>] which can be used to update multiple elements of an array which match an array filter condition. You can read more about this operator here: https://docs.mongodb.com/manual/reference/operator/update/positional-filtered/

For example, to update all elements of the variants array where sku is "abc123" across every document in the collection:

db.collection.update({}, { $set: { "variants.$[el].inventory": 25 }}, { multi: true, arrayFilters: [{ "el.sku": "abc123"}] })

Unfortunately I'm not aware of any way in a single query to update a document's field based on whether another field in the document was updated. This is something you would have to implement with some client-side logic and a second query.

EDIT (thanks to Asya's comment):

You can do this in a single query by only matching documents which will be modified. So if nMatched and nModified are necessarily equal, you can just set updated to true. For example, I think this would solve the problem in a single query:

db.collection.update({ variants: { $elemMatch: { inventory: { $ne: 25 }, sku: "abc123" } } }, { $set: { "variants.$[el].inventory": 25, updated: true }}, { multi: true, arrayFilters: [{ "el.sku": "abc123"}] })

First you match documents where the variants array contains documents where the sku is "abc123" and the inventory does not equal the number you are setting it to. Then you go ahead and set the inventory on all matching subdocuments and set updated to true.

tfogo
  • 1,416
  • 1
  • 14
  • 23
  • It's too bad it can't be done in a single operation but this is great information. I used your solution to update what I needed and then based on the "nModified" value that gets returned, I run another query that updates "updated". Thanks! – ChrisC Mar 10 '18 at 21:54
  • actually it *can* be done in a single update - you just have to be careful/clever in your update query predicate instead of using empty predicate. It will also make it faster. – Asya Kamsky Aug 09 '18 at 22:36
  • If you craft your query to only match documents which need to be updated then you can $set updated to true on those documents. – Asya Kamsky Aug 10 '18 at 00:22
  • Ah of course! I think I see what you're saying. I just updated the answer to show an example of what I think you're describing. Would you mind taking a look to see if it's what you had in mind? – tfogo Aug 10 '18 at 01:02