Couchbase N1QL: Update document attribute based on another attribute

Question

I'm struggling with an update-for N1QL query

my documents look like this :

{
"invalidityReasons": {
    "AU": [
      "ANCESTOR_DEACTIVATED_OR_INVALID"
    ],
    "BE": [
      "ANCESTOR_DEACTIVATED_OR_INVALID"
    ],
    "BG": []
}
"metadata": {
  "configurations": {
    "AU": {
      "enabled": true,
    },
    "BE": {
      "enabled": false,
    },
    "BG": {
      "enabled": true,
    }
  }
}
}

what I'm trying to do :

for all countries which has the invalidityReason "ANCESTOR_DEACTIVATED_OR_INVALID" and is activated (ie "enabled": true in metadata.configurations) => deactivate this country (ie "enabled": false)

in the example above, AU (Autralia) should be deactivated

where I am now

this means in my head :

go thought all countries
find those with invalidityReasons = ANCESTOR_DEACTIVATED_OR_INVALID + enable = true
update activation

so the update-for looked quite good but I can't manage to make it work :

update `data` t
SET  t.metadata.configurations.[country].enabled = false
FOR country IN OBJECT_NAMES(t.metadata.configurations)
    when t.metadata.configurations.[country].enabled = true
    and array_contains(t.invalidityReasons.[country], 'ANCESTOR_DEACTIVATED_OR_INVALID') end;

Couchbase announces mutations but my countries are still activated

do you have any idea :

if this is even possible with N1QL ?
what am I doing wrong ? :)

by the way I'm using Couchbase EE 5.1.1

thanks, cheers,

Jules

vsr vsr · Accepted Answer · 2018-09-06T13:46:20

It should work. The following working correctly on single document.

insert into default values("11",{ "invalidityReasons": { "AU": [ "ANCESTOR_DEACTIVATED_OR_INVALID" ], "BE": [ "ANCESTOR_DEACTIVATED_OR_INVALID" ], "BG": [] }, "metadata": { "configurations": { "AU": { "enabled": true }, "BE": { "enabled": false }, "BG": { "enabled": true } } } } );
SELECT * FROM default USE KEYS "11";
update `default` t USE KEYS "11"
SET  t.metadata.configurations.[country].enabled = false
FOR country IN OBJECT_NAMES(t.metadata.configurations)
    when t.metadata.configurations.[country].enabled = true
    and array_contains(t.invalidityReasons.[country], 'ANCESTOR_DEACTIVATED_OR_INVALID') end;
SELECT * FROM default USE KEYS "11";

Couchbase N1QL: Update document attribute based on another attribute

1 Answers