Document update using N1QL

Hello,

I am using N1QL to carry out a bulk update operation. We have a a json document which is something like this:

“data”: {
“R” : “1”,
“M” : “2”,
“E” : “3”,
“A”: “4”
}
“toUpdate”: “before-val”

The N1QL I run is:
update bucket set toUpdate = “after-val” where…;

The document “toUdpate” field gets updated correctly, but in the process, the map that we have in the document also changes, probably because the Map implementation in JsonDocument is SortedMap. So, the JSON after running the N1QL looks like this:

“data”: {
“A” : “4”,
“E” : “3”,
“M” : “2”,
“R”: “1”
}
“toUpdate”: “after-val”

This change of sequence of map entries makes one of our use case fail. Is there anyway that we can get around this? We do want to use N1QL for bulk updates, instead of fetching all documents client side, and then replacing them.

N1QL sorts fields in the object and you can’t avoid it.

You can use N1QL to get document keys and explore SDKs to update using subdoc API.

I’d agree with @vsr1, that the SubDocument API may be what you should look into if you’re looking to reduce transport. Two other quick notes…

JSON (versus XML, as an example) explicitly says fields in objects are unordered. If the change of the order is breaking other parts of your app, then you’re not really using JSON correctly. Order is preserved in arrays, and you should use arrays if you need ordering. From IETF RFC-8259:

An object is an unordered collection of zero or more name/value
pairs, where a name is a string and a value is a string, number,
boolean, null, object, or array.

Also note that if you bulk-fetch-and-modify from app code, it’s no more costly than how N1QL does it, as N1QL is a client and does the same thing. You’ll likely need to write more code, but execution (if you have the doc IDs) is approximately the same amount of work.

1 Like

Thank you ingenthr, we realize that changing of field order in the json document should not impact our code/use-case and we are working on that. I will explore the sub document API further as it looks promising.