Using N1ql to update/replace an array element

SQL++
1

Hi,

I have following doc:

{
  "_type": "Category",
  "name": "Vehicle",
  "enabled": true,
  "subcategories" : [{
      "_id": "1",
      "name": "Car",
      "enabled": true,
      ...
  },{
      "_id": "2",
      "name": "Motorcycle",
      "enabled": true,
      ...
  },{
      "_id": "3",
      "name": "Plane",
      "enabled": true,
      ...
  }]
}

now i want to update/replace the whole subcategory where id = “3” with the following object:

{ "_id": "3", "name": "Another Plane name", "enabled": false, ... }

I tried:

UPDATE default
SET sb = { "_id": "3", "name": "Another Plane name", "enabled": false, ... } FOR sb IN subcategories WHEN sb._id = "3" END
WHERE _type = "Category"

But it’s not working. Any idea?

2

Try this.

UPDATE default
SET subcategories[i] = { ... } FOR i: sb IN subcategories WHEN sb._id = "3" END
WHERE _type = "Category";
3

It’s working!. Thanks!

One last thing. Is it posible to do the same thing with a nested array inside “subcategories” ?

For example:

{
  "_type": "Category",
  "name": "Vehicle",
  "enabled": true,
  "subcategories" : [{
      "_id": "1",
      "name": "Car",
      "enabled": true,
      "subsubcategories" : [{
          "_id": "4",
          "name": "Microcar",
          "enabled": true,
          ...
      },{
          "_id": "5",
          "name": "Economy Car",
          "enabled": true,
          ...
      },{
          "_id": "6",
          "name": "Compact car",
          "enabled": true,
          ...
      }]
  },{
      "_id": "2",
      "name": "Motorcycle",
      "enabled": true,
      ...
  },{
      "_id": "3",
      "name": "Plane",
      "enabled": true,
      ...
  }]
}

How can i update/replace the subsubcategory with id = “5” ?

4

Yes, you can chain / nest the FOR clauses.

5

Can you give me any example of it? I tried with this, but had no luck:

UPDATE default
SET sc.subsubcategories[i] = {"_id": "5","name": "Economy Car 2","enabled": false}
FOR i: ssc IN (ARRAY sc.subsubcategories FOR sc IN subcategories WHEN sc._id = "1" END) WHEN ssc._id = "5" END
WHERE _type = "Category";

Thanks.

6

Close. Try this.

UPDATE default
SET sc.subsubcategories[i] = { ... } FOR i: ssc IN sc.subsubcategories FOR sc IN subcategories WHEN ssc._id = "5" END
WHERE _type = "Category";
7

It’s not working :\ .

8

What is it doing? Are you on 4.5.1?

9

Yes,

I’m running:

UPDATE default
SET sc.subsubcategories[i] = {"_id": “5”,“name”: “Economy Car 2”,“enabled”: false}
FOR i: ssc IN sc.subsubcategories FOR sc IN subcategories WHEN ssc._id = “5” END
WHERE _type = "Category"
RETURNING default.*;

But it’s having no effect.

10

Ok. Taking a look now.

11

Hi @geraldss,

Did you find out what the problem was?

12

Hi @ender3, not yet. Working on it. We implemented this feature for this exact use case.

13

You can use the following for now, which is equivalent. I am still troubleshooting the other syntax. Sorry about that.

UPDATE default d
SET sc.subsubcategories = ARRAY CASE WHEN ssc._id = "5" THEN {} ELSE ssc END FOR ssc IN sc.subsubcategories END FOR sc IN subcategories WHEN sc._id = "1" END
WHERE _type = "Category"
RETURNING d.*;
14

Hi @ender3, it turns out you found a bug. Ticket filed here.

Will try to fix this for 4.6. Thanks.