Update an attribute inside a array in a json docuemnt

n1ql

#1

I want to update an attribute inside an array present in a json document.

Ex: My json document sample:
{
“lineItemID”: 5162,
“metadata”: [
“film”
],
“publishDate”: “2017-11-04”,
“publishedBy”: “”,
“referenceJson”: [
{
“referenceId”: “ASC 926-20-50-2”,
"referenceUrl": "https://www.inform.pwc.com/inform2/show?action=informContent&id=0110031362581808#FASB_COD_926_20_50_2"
}
],
“standardid”: “ASC 926-20-50-2”,
“status”: “APPROVED”
}

I want to update the referenceUrl present in the referenceJson.

My select query being :

select js.referenceUrl from ChecklistLibrary ch unnest referenceJson js
where meta(ch).id like “For Profit%” and ch.lineItemID = 5162

I tried to update using the script:

update ChecklistLibrary
set js.referenceUrl =“http://www.inform.pwc.com/inform2show?action=informContent&id=0110031369654710#FASB_COD_926_20_50_2
WHERE referenceUrl = (select js.referenceUrl from ChecklistLibrary ch unnest referenceJson js
where meta(ch).id like “For Profit%” and ch.lineItemID = 5162)
and meta().id like “For Profit%” and lineItemID =5162 ----->but it didnot work…

Can you help me with the update script?


Remove node in deep nested tree
#2
INSERT INTO default VALUES ("k1", { "lineItemID": 5162, "metadata": [ "film" ], "publishDate": "2017-11-04", "publishedBy": "", "referenceJson": [ { "referenceId": "ASC 926-20-50-2", "referenceUrl": "https://www.inform.pwc.com/inform2/show?action=informContent&id=0110031362581808#FASB_COD_926_20_50_2" } ], "standardid": "ASC 926-20-50-2", "status": "APPROVED" });

Update all array elements

UPDATE default AS d
SET o.referenceUrl = "xxxxx" FOR o IN d.referenceJson END
WHERE d.lineItemID = 5162;

Update specific Array element based on condition

UPDATE default AS d
SET o.referenceUrl = "yyy" FOR o IN d.referenceJson WHEN o.referenceId = "ASC 926-20-50-2" END
WHERE d.lineItemID = 5162 AND ANY v IN d.referenceJson SATISFIES v.referenceId = "ASC 926-20-50-2" END;

WHERE clause decides which document to update (ANY condition avoids unnecessary mutations). FOR in SET clause decides which object in array need to update.