Merge old and new document in update query

SQL++
1

I would like to use n1ql UPDATE statement to partially update all documents of a certain type. I would like to merge the old attributes with the new ones from a partial update json document so that all the old values are preserved.

A full document would look like:
{
“id”:1,
“birthDate”: “2013-06-15”,
“defaultClient”:{},
“firstName”:“Peter”,
“documentTypeID”: {},
“firstName”: “irure velit cupidatat do”,
“surname”: “consequat ut cillum culpa”,
“middleName”: “Duis sit mollit esse”,
“nationality”: “sunt”,
“personalNumber”: “fugiat esse nisi voluptate”,
“title”: “laborum commodo ipsum deserunt”,
“titleAfter”: “ullamco nulla es”
}

A partial update document could look like the following (it is user generated, attributes can vary):
{“defaultClient”:{“clientId”:“ID123453”},“firstName”:“John”}

I would like like to achieve the following - (does not work):
UPDATE documents doc SET doc=MERGE(doc, {“defaultClient”:{“clientId”:“ID123453”},“firstName”:“John”}) WHERE type=‘User’ AND condition>1

In another words I am looking for some way how to merge the new partial update into the old object - if the properties do not exists just create them, else update them. I do not control how a partial update document looks like and what attributes it has - it is user provided …

2

You can try this. This will not work for recursive nested levels

UPDATE documents doc 
SET doc = OBJECT_CONCAT(doc, {"defaultClient":{"clientId":"ID123453"},"firstName":"John"}) WHERE type="User" AND condition > 1
1 Like