Update all documents in a collection based on a property which is under a dynamic field

Working with Data
#1

this is my entire collection, i want to update the rating “.90” where current rating is “.80”

[{
  "_id": 1,
  "242342342": {
    "rating": ".95",
    "refreshed": false
  }
},{
  "_id": 2,
  "242342424": {
    "rating": ".80",
    "refreshed": false
  }
},{
  "_id": 3,
  "2342342342": {
    "rating": ".80",
    "refreshed": false
  }
},{
  "_id": 4,
  "234234234": {
    "rating": ".95",
    "refreshed": false
  }
}]
#2

You are making your life difficult by using dynamic field keys.

Are your fields limited to

242342424

and

2342342342

You should be using the attribute pattern for something like this.

What do you write as a query right now to find out all documents with rating .80?

#3

no, its can be any number,

right now just load everything using python and checking it there

#4

Lets fix

first because it is needed anyway for the update.

The following aggregation pipeline should provide with a better way to

[
    { "$set" : {
        "_tmp.root" : { "$objectToArray" : "$$ROOT" }
    } } ,
    { "$match" : {
        "_tmp.root.v.rating" : ".80"
    } } ,
    { "$unset" : "_tmp" }
]

Much faster that downloading everything but still sub-optimal since you cannot use an index.

But since

there is no way to use an index.

Carefully read about the attribute pattern and get rid of these numbers as keys.

Now that we have a way to get only the documents to update, you are able to do, with a python loop, a sub-optimal bulk write with one updateOne operation for each _id that you have to update.

I still have to work on how to do it directly with aggregation but it needs some work. It will involve, I think, some $filter (to only keep v.rating:.80 from _tmp.root) , an $arrayToObject, a $mergeObject (to update $$ROOT) and then a $merge stage to commit the update. UGLY

Carefully read about the attribute pattern and get rid of these numbers as keys.

#5

I had some time to play with the next stages for the update. The stages are to be added between the $match and the $unset.

/* filter to only keep the dynamic key to update */
filter = { "$set" : {
    "_tmp.filtered" : { "$filter" : {
        "input" : "$_tmp.root" ,
        "cond" : { "$eq" : [ "$$this.v.rating" , ".80" ] }
    } }
} }

/* extract first and only element since it is easier to update */
extract = { "$set" : {
    "_tmp.extracted" : { "$arrayElemAt" : [ "$_tmp.filtered" , 0 ] }
} }

/* update the extracted v: to desired value */
update = { "$set" : {
    "_tmp.updated" : [ {
        "k" : "$_tmp.extracted.k" ,
        "v" : { "rating" : ".90" , "refreshed" : "$_tmp.extracted.v.refreshed" }
    } ]
} }

/* make the updated dynamic an object ready to merge */
objectify = { "$set" : {
    "_tmp.object" : { "$arrayToObject" : "$_tmp.updated" }
} }

/* The magic replace that really update the original dynamic field in the root object. */
replace = { "$replaceWith" : {
    "$mergeObjects" : [ "$$ROOT" , "$_tmp.object" ]
} }

You could surely implements all the above $set stages into one big ugly very deep single expression of the $replaceWith that does not use _tmp variables, that is hard to read and hard to debug but I won’t.

The next stage commits the result back to the original stored document. It has to be performed after the $unset otherwise the temporary values will also be stored back.

merge = { "$merge" : {
    "into" : "the_name_of_your_collection" ,
    "on" : "_id"
} }

I also want to mentioned. Carefully read about the attribute pattern and get rid of these numbers as keys.

And use numbers for your rating numbers. Otherwise you cannot apply arithmetic operations without converting.