CODEWITHSUNDEEP
couchbasesql++
jderegnaucourt
jderegnaucourt

Reputation: 65

Update multiple elements of a list using Couchbase N1QL

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 ? :)

thanks guys,

Upvotes: 4

Views: 1069

Answers (2)

vsr
vsr

Reputation: 7414

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. 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.

Upvotes: 2

Johan Larson
Johan Larson

Reputation: 1890

This query should work:

update data
use keys "03c53a2d-6208-4a35-b9ec-f61e74d81dab"
set country.enabled = true for country within metadata.configurations when 
    country.enabled is defined end

The WITHIN allows "country" to be found at any level of the metadata.configurations structure, and we use the "WHEN country.enabled IS DEFINED" to make sure we are looking at the correct type of "country" structure.

Upvotes: 1

Related Questions