Update multiple elements of a list using Couchbase N1QL

query

#1

context

I have somewhere in my couchbase documents, a node looking like this :

"metadata": {
    "configurations": {
        "AU": {
         "enabled": false,
         "order": 2147483647
        },
        "BE": {
         "enabled": false,
         "order": 2147483647
        },
        "BG": {
         "enabled": false,
         "order": 2147483647
        } ...
    } 
}

and it goes along with a list country unicodes and their “enabled” state

what I want to achieve

update this document to mark is as disabled (“enabled” = false) for all countries

to do this I hoped this syntax would work (let’s say I’m trying to update document with id 03c53a2d-6208-4a35-b9ec-f61e74d81dab)

UPDATE `data` t 
SET country.enabled = false
FOR country IN t.metadata.configurations END
where meta(t).id = "03c53a2d-6208-4a35-b9ec-f61e74d81dab";

but it seems like it doesn’t change anything on my document

any hints ? :slight_smile:

thanks guys,


#2

This is answered over here

You can use WITHIN clause as @johan_larson suggested in above link.

SET … FOR IN… END syntax is for Arrays. In your case it is OBJECT. FOR v WITHIN makes iterate each sub objects . Or Generate Array on the fly.

If you know which level of object you are updating you can try this

As the filed name is dynamic you can generate field names using OBJECT_NAMES() and use that during update of field.

UPDATE data t USE KEYS "03c53a2d-6208-4a35-b9ec-f61e74d81dab" 
      SET  t.metadata.configurations.[v].enabled = false FOR v IN OBJECT_NAMES(t.metadata.configurations) END ;

In above example OBJECT_NAMES(t.metadata.configurations) generates [“AU”, “BE”,“BG”] When field of JSON is referenced .[v] it evaluates v and value become field (special construct no filed name after dot and starts array brackets) The v can be any expression and evaluate to non empty string identifier. So During looping construct t.metadata.configurations.[v].enabled becomes

t.metadata.configurations.`AU`.enabled, 
t.metadata.configurations.`BE`.enabled, 
t.metadata.configurations.`BG`.enabled

Depends on value of v.


#3

Thank you very much, issue marked as solved on Stackoverflow. :slight_smile:
Have a nice day,
Juels