Update query to move object from one array to another in same document

Couchbase Server
,
1

Hi I’m new to N1QL, can anyone help me with update query.
Below is the document structure.

{
"id" : "0123",
"arr1" [
{
"number" : 1,
"name" : "abc"
},
{
"number" : 2,
"name" : "def"
},
{
"number" : 3,
"name" : "ghi"
}
] ,
"arr2" [
{
"number" : 4,
"name" : "xyz"
}
] 
}

Want to move 2 objects from arr1 (with id 2,3) to arr2.

Final Output :

{
"id" : "0123",
"arr1" [
{
"number" : 1,
"name" : "abc"
}
] ,
"arr2" [
{
"number" : 4,
"name" : "xyz"
},
{
"number" : 2,
"name" : "def"
},
{
"number" : 3,
"name" : "ghi"
}
] 
}
2

You could try:

UPDATE myCollection 
SET arr2 = ARRAY_CONCAT(arr2,ARRAY v FOR v IN arr1 WHEN v.`number` IN [2,3] END)
   ,arr1 = ARRAY v FOR v IN arr1 WHEN v.`number` NOT IN[2,3] END
WHERE id = "0123"

All this does is select the elements from arr1 matching the number (quotes are necessary as it is a reserved word) criteria and concatenate them with arr2. To fulfil the move, the arr1 is reconstructed containing only the elements not matching the number field selection criteria.

HTH.

1 Like