Updating array via N1QL

SQL++
1

Hi,

I have a document like this:

{
  "a": "1",
  "b": [
    {
      "c": "IN"
    },
    {
      "c": "OUT"
    },
    {
      "c": "IN"
    }
  ]
}

The array can have 1, 2, 3 or more object as part of b attribute. It will always have minimum one object. If it has just one element, then the value appended should be "e": "last"

I need a n1ql query which updates the json object in the b array with "e":"last" and the previous elements with "e": "intermediate".

Something like this:

{
  "a": "1",
  "b": [
    {
      "c": "IN",
      "e": "intermediate"
    },
    {
      "c": "OUT",
      "e": "intermediate"
    },
    {
      "c": "IN",
      "e": "last"
    }
  ]
}

Thanks,
Himanshu

2

I can think of simple way , using 2 separate queries

first

UPDATE `test` AS h
SET i.e="last" FOR i IN b END
RETURNING h;

second - update all except last entry

UPDATE `test` AS h
SET i.e="intermediate" FOR i IN b[:-1] END
RETURNING h;
3

Can this be achieved in one n1ql query?

4

after looking at UPDATE | Couchbase Docs

the 2 queries can be combined like

UPDATE `test` AS h
SET i.e="last" FOR i IN b END,
 i.e="intermediate" FOR i IN b[:-1] END
RETURNING h;
5

Extending that, you can also use:

UPDATE `test` h                                                                                                                    
SET elem.e = "intermediate" FOR elem IN h.b[:-1] END
    ,h.b[-1].e = "last"
WHERE ...
1 Like
6

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.