Update two attributes in an array element

SQL++
1

Hi,

I have this document in couchbase 7.1.1 enterprise edition

{
  "id": 1,
  "cities": [
    {
      "code": 1,
      "name": "Berlin"
    },
    {
      "code": 2,
      "name": "Frankfurt"
    }
  ]
}

I want to update the code and name properties of all cities that have code = 1 and name = “Berlin” to code = 11 and name = “BLN”

I thought that this update statement would do it

UPDATE mybucket
SET c.code = 11, c.name = "BLN" FOR c IN cities 
WHEN c.code = 1 AND c.name = "Berlin" END
WHERE id = 1

When I run the update statement in the query editor only the name property is changed to “BLN”, but not the code property

What am I doing wrong?

Regards,
Faris

2

The update-for clause is per field you want to update, as shown in the syntax diagram here.

So the statement you’re after is:

UPDATE myBucket
SET c.code = 11 FOR c IN cities WHEN c.code = 1 AND c.name = "Berlin" END
,c.name = "BLN" FOR c IN cities WHEN c.code = 1 AND c.name = "Berlin" END
WHERE id = 1;

The conditions are evaluated on the “before image” so the first field’s update doesn’t affect the condition for the second field.

HTH.

3

You can also use

UPDATE myBucket AS b
SET b.cities[pos] = OBJECT_CONCAT(c, {"code":11, "name":"BLN"}) FOR pos:c IN b.cities WHEN c.code = 1 AND c.name = "Berlin" END
WHERE id = 1;