Positonal updates in MongoDB

Question

I am trying to update an array of sub-documents in MongoDB. I know that the multiple updates in a single query is not supported by the positional operator $.

Sample document :

{
  "_id": ObjectId("559a6c281816ba598cdf96cd"),
  "collections": [
    {
      "id": 12,
      "name": "a"
    },
    {
      "id": 12,
      "name": "b"
    }
  ]
}

I need to update the id : 12 sub-documents with an extra field "price" : 12 . I tried the following query but the same sub-document that matches is gettting updated so I have added an extra condition "price" : {$exists : false} and also tried "price" : {$ne : 12}. When I add the "price" : {$exists : false} no document is getting returned. I am using PyMongo and python . So do I need to perform the updates in the python code and update the document. Is there any workaround for this?

Tried query :

db.scratch.update({ "collections.id" : 12 } , {"$set" : {"collections.$.price" : 12 }})

Tried it with above combinations of price : false, price: {$exists : false} but they also don't work. But I keep getting back the message that one document is updated. I am using mongo-hacker in my mongo shell.

I am building a migration tool wherein all the customer info is present as a single document.

{
  "_id": ObjectId("559a2d9bfffe043444c72889"),
  "age": NumberLong("23"),
  "customer_address": [
    {
      "type": "Work",
      "verified": true,
      "address": "1A NY"
    }
  ],
  "customer_id": NumberLong("3"),
  "customer_orders": [
    {
      "order_date": ISODate("2015-01-01T00:12:01Z"),
      "order_id": NumberLong("2"),
      "product_id": NumberLong("234")
    },
    {
      "order_date": ISODate("2015-12-01T00:00:00Z"),
      "order_id": NumberLong("3"),
      "product_id": NumberLong("245")
    },
    {
      "order_date": ISODate("2015-12-21T00:00:00Z"),
      "order_id": NumberLong("4"),
      "product_id": NumberLong("267")
    },
    {
      "order_id": NumberLong("5"),
      "order_date": ISODate("2015-12-29T00:00:00Z"),
      "product_id": NumberLong("289")
    },
    {
      "order_id": NumberLong("9"),
      "order_date": ISODate("2015-02-01T00:12:05Z"),
      "product_id": NumberLong("234")
    }
  ]
}

I get the basic info from customer table, address from customer address table and the product log from another table related by foreign key reference in MySQL. Now I want to update the product id with the correct name and price so that I can get a view of the customer instead of doing a query to get the corresponding price for the product id and also since join is not present in

{
  "_id": ObjectId("559a2d9bfffe043444c72889"),
  "age": NumberLong("23"),
  "customer_address": [
    {
      "type": "Work",
      "verified": true,
      "address": "1A NY"
    }
  ],
  "customer_id": NumberLong("3"),
  "customer_orders": [
    {
      "name": "Brush",
      "order_date": ISODate("2015-01-01T00:12:01Z"),
      "order_id": NumberLong("2"),
      "product_id": NumberLong("234"),
      "price": 12
    },
    {
      "order_date": ISODate("2015-12-01T00:00:00Z"),
      "order_id": NumberLong("3"),
      "product_id": NumberLong("245")
    },
    {
      "order_date": ISODate("2015-12-21T00:00:00Z"),
      "order_id": NumberLong("4"),
      "product_id": NumberLong("267")
    },
    {
      "order_id": NumberLong("5"),
      "order_date": ISODate("2015-12-29T00:00:00Z"),
      "product_id": NumberLong("289")
    },
    {
      "name": "Brush",
      "order_id": NumberLong("9"),
      "order_date": ISODate("2015-02-01T00:12:05Z"),
      "product_id": NumberLong("234"),
      "price": 12
    }
  ]
}

Tried queries :

db.customer.update({"customer_orders.product_id" : 234 , "customer_orders.name" : {$exists : false}}, {"$set" : {"customer_orders.$.name" : "Brush", "customer_orders.$.price" : 12} } )

returns 0 documents updated.

db.customer.update({"customer_orders.product_id" : 234 , "customer_orders.name" : {$exists : true}}, {"$set" : {"customer_orders.$.name" : "Brush", "customer_orders.$.price" : 12} } )

returns 1 document updated but even after sequential execution of the same command only the first field is getting updated. So is there a workaround or do I need to do my update in the Python client?

Answer 1

There really isn't much for this than to read the document in order to find out how many array element there are and update them by index (or the distinct "id" value, but it doesn't really matter beyound reading the object first.

In the safest way, don't alter the whole document and "save" it back, but perform an update per array element:

id = ObjectId("559a2d9bfffe043444c72889")
doc = coll.find_one({ "_id": id  })

for idx, el in enumerate(doc["customer_orders"]):
    if ( el["product_id"] == 234 ):
        update = { "$set": {} }
        update["$set"]["customer_orders."+str(idx)+".price"] = 12
        update["$set"]["customer_orders."+str(idx)+".name"] = "Brush"
        coll.update({ "_id": id },update)

You can make that a bit more efficient with bulk operations:

id = ObjectId("559a2d9bfffe043444c72889")
doc = coll.find_one({ "_id": id  })
bulk = coll.initialize_ordered_bulk_op()

for idx, el in enumerate(doc["customer_orders"]):
    if ( el["product_id"] == 234 ):
        update = { "$set": {} }
        update["$set"]["customer_orders."+str(idx)+".price"] = 12
        update["$set"]["customer_orders."+str(idx)+".name"] = "Brush"
        bulk.find({ "_id": id }).update(update)

bulk.execute()

Which at least sends all updates at once to the server

But the general course is that you need to identify the "exact" element either by index of other unique identifier in order to send the correct update position.

Trying something like

{ "customer_orders.price": { "$exists": False } }

or

{ "customer_orders.product_id": 234 }

Is going to hit the following problems:

• It would match multiple things anyway in both cases in general
• For $exists, is not acceptable for a positional $ match operation, only exact value matches produce an index for update.

So read the "exact" id or position indexes from the document itself and then process the update.