Update an attribute inside a array in a json docuemnt

SQL++
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?

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.

3

Hi vs1,

I need to update 2 attributes inside object in array

This works for me tho I’m not sure if this is the best practice:

UPDATE default module
USE KEYS "MODULE_1"
SET o.title = "Sample Added Field 3 Edited"
    FOR o IN module.fields WHEN o.row = 3 AND o.section = 2 END,
    o.field = "addedField_3_edited"
    FOR o IN module.fields WHEN o.row = 3 AND o.section = 2 END;
4

@nacion.emelson

If condition is different or SET/UNSET that only option (Each SET/UNSET different FOR syntax).
In your case it same you can do this also

UPDATE default  AS module
USE KEYS "MODULE_1"
SET  module.fields[pos] = OBJECT_CONCAT (o, {"title":"Sample Added Field 3 Edited", "field":"addedField_3_edited"})
FOR pos:o IN module.fields
WHEN o.row = 3 AND o.section = 2 END;

Also you can use cascade OBJECT_PUT(), OBJECT_ADD(),… instead of OBJECT_CONCAT() Object Functions | Couchbase Docs

1 Like
5

This works!

Thanks, vsr1