Updating count of items in list based on item name

Working with Data
, ,
1

I am new to MongoDB and not able to generate a query to update items in a list based on the item names.
My example object is:
Inventory:

{ _id: "1234",
  name: "test",
  items: [
   { "name": "pen", "count": 4}, {"name": "copy", "count": 10}, {"name": "book", "count": 2}
  ]
}
{ _id: "4567",
  name: "test2",
  items: [
   { "name": "pen", "count": 1}, {"name": "copy", "count": 1}, {"name": "book", "count": 10}
  ]
}

I want to update the data based on the inventory id and the name of the item. Update can be for multiple items but only for 1 inventory.
Example Update request:

{ _id: “1234”,
data: { “pen”: 1, “copy”:-5}
}

And the result Inventory should be:

{ _id: "1234",
  name: "test",
  items: [
   { "name": "pen", "count": 5}, {"name": "copy", "count": 5}, {"name": "book", "count": 2}
  ]
}

I tried and created a query, but facing issue to make it dynamic:

def _prepare_query(data):
update_query = {
        "$set": {
            "items": {
                "$map": {
                    "input": "$items",
                    "as": "item",
                    "in": {
                        "$cond": [
                            {"$eq": ["$$item.name", {"$in": list(data.keys())}]},
                            {
                                "name": "$$item.name",
                                "count": {
                                    "$inc": ["$$item.count", data["$$item.name"]]
                                },
                            },
                            "$$item",
                        ]
                    },
                }
            }
        }
    }

where, data is the input dict

data: { "pen": 1, "copy":-5}

This is throwing KeyError: “$$item.name”, which is obv as data doesn’t have a key with that string.
Can I not prepare the query outside the db.collection.updateOne() call?
Any help would be appreciated.
Thanks

2

@Kushagra_Kesav, Can you please help?

3

To update

to

using

the updateOne call will need the parameters

query = { "_id" : "1234" }
filters = { "arrayFilters" : [
    { "pen.name" : "pen" } ,
    { "copy.name" : "copy" }
] }
inc = { "$inc" : {
    "items.$[pen].count" : 1 ,
    "items.$[copy].count" : - 5
} }
inventory.updateOne( query , inc , filters )

Your _prepare_query can use JS map to create both the inc and filters variables.

2 Likes
4

Thank you very much. This helped a lot.
Also, just for other people reference, the arrayFilters top-level field name must be an alphanumeric string beginning with a lowercase letter.

1 Like
5

Hi @steevej,
Can you also please help to change the query such that if the item is not present in the original document, it should add that item to the final result document with count as provided?

(Apologies I am extremely new to MongoDB.)

6

I found something like this:

db.exp.update( { _id: "1234" },
  [ 
      { 
          $set: { 
              Items: {
                  $reduce: {
                      input: { $ifNull: [ "$Items", [] ] }, 
                      initialValue: { items: [], update: false },
                      in: {
                          $cond: [ { $eq: [ "$$this.name", INPUT_DOC.name ] },
                                   { 
                                     items: { 
                                        $concatArrays: [
                                            "$$value.items",
                                            [ { name: "$$this.name", count: { $inc: [ "$$this.count", INPUT_DOC.count ] } } ],
                                        ] 
                                      }, 
                                      update: true
                                   },
                                   { 
                                      items: { 
                                         $concatArrays: [ "$$value.items", [ "$$this" ] ] 
                                      }, 
                                      update: "$$value.update" 
                                   }
                          ]
                      }
                  }
              }
          }
      },
      { 
          $set: { 
              Items: { 
                  $cond: [ { $eq: [ "$Items.update", false ] },
                           { $concatArrays: [ "$Items.items", [ INPUT_DOC ] ] },
                           { $concatArrays: [ "$Items.items", [] ] }
                  ] 
              }
          }
      }
  ] 
)

But I don’t know how to iterate over INPUT_DOC, if INPUT_DOC is a list of items.

7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.