Update nested array of objects (CRUD)

SQL++
1

I have an document ‘Test-Doc’ containing the following json

{
“id”: “1”,
“items”: [
{
“createId”:“123”,
“day” : “Sun”
},
{
“createId”: “2”,
“day”: “sd”
},
{
“createId”: “33”,
“day”: “fdf”
},
{
“createId”: “43”,
“day”: “sfd”
}
],
“name”: “test3”
}

I want to remove a specific object from ‘items’ array. Lets say i want to remove an object
{ ‘createId’: ‘43’, ‘day’: ‘sfd’ } from ‘items’. How can i do this ?

Given the result should be

{
“id”: “1”,
“items”: [
{
“createId”:“123”,
“day” : “Sun”
},
{
“createId”: “2”,
“day”: “sd”
},
{
“createId”: “33”,
“day”: “fdf”
}
],
“name”: “test3”
}

2

yes, you can use ARRAY_REMOVE() function, for example

UPDATE default USE KEYS ["1"] 
   SET items = ARRAY_REMOVE(items,{
                                   "createId": "43",
                                   "day": "sfd"
                            }) RETURNING *;
3

If you are not able to supply whole object you can reconstruct the object like below.

UPDATE default
   SET items =  ARRAY v FOR v IN items WHEN v.createId NOT IN ["33"] END
   WHERE ANY v1 IN items SATISFIES v1.createId IN ["33"] END;

With WHERE clause only documents that actually required will be mutated. Without WHERE clause mutation happens on all documents even the underneath document no update happens. (example if you change IN clause to “XX” and without WHERE clause it updates with same value. Which is Unnecessary). SET clause is conditional, better performance you want to make UPDATE conditional first, then make SET conditional based on what you need to update.

For nested array updates look last example ( UPDATE … FOR …)

https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/update.html

1 Like
4

UPDATE myBucketname
SET items = ARRAY i FOR i IN items WHEN i.createId != “43” END
WHERE META().id LIKE “Test-Doc” AND
ANY i IN items SATISFIES i.createId = “43” END;

This worked for me ! You can correct me if this is not a optimized approach.