I’m having trouble understanding how to craft a N1QL query to perform an upsert with the elements(json objects) of an array in the given document below (bucket X, meta(x).id = 1256.
{
"Note:": "dummy test data",
"userId": 1256,
"incentivePrograms": [
{
"incentiveProgramId": "a22087dc-81a0-4604-8559-733ffa0f7845",
"userMembershipId": "23dvc",
"membershipStatus": "active"
}
],
"incentiveProgramPreferences": [
{
"universityId": "3056",
"IncentiveProgramID": "a22087dc-81a0-4604-8559-733ffa0f7845"
}
]
}
The array I want to merge an array element into for this document is the “incentivePrograms”. The merge should, on query parameter “ip_id” matching “IncentiveProgramId” value, the object is replaced with a new object, on no matching elements the new object would be appended to the array.
I can’t even get an append to work (I get no errors and empty results in the workbench):
SELECT ARRAY_APPEND(im.incentivePrograms,{
"incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d",
"userMembershipId": "dud",
"membershipStatus": "inactive"
})
FROM default:`X` im
WHERE META(im) = 1256
I also tried this:
UPDATE default:`X` im
SET im.incentivePrograms = ARRAY_APPEND(im.incentivePrograms, {
"incentiveProgramId": "1422e858-bae7-48ab-befe-88e9a95ced2d", "userMembershipId": "dude", "membershipStatus": "inactive" }
)
WHERE META(im) = 1256
Which is patterned after on of the couchbase doc pages.
Do I need to use an UNNEST somehow, or some special subquery, or CASE structure somehow?
I understand that upserts can only be done at the document level, and not within the document.