Nested collection update

SQL++
,
1

Hi there.
I have set of documents with following structure:

{
  "someData" : "someValue",
  "val" : "a",
  "nested" : [
    {
      "name" : "row1",
      "value" : "a"
    },
    {
      "name" : "row2",
      "value" : "b"
    }
  ]
}

How would I write UPDATE statement, so “nested” would remain only rows where “nested.value = val”?
In this case after and update statement I would like to have this result:

{
  "someData" : "someValue",
  "val" : "a",
  "nested" : [
    {
      "name" : "row1",
      "value" : "a"
    }
  ]
}

PS rows are not sorted, so I need a generic solution of removing row from nested collection.
Unfortunately none of the following links helped me:

Thanks.

2

try this:

UPDATE  default USE KEYS ["k2","k1"] SET nested = ARRAY i FOR i IN nested WHEN i.`value` = "a" END
RETURNING meta().id, *;

Note that the field ‘value’ in your document is reserved word, so need to escape that. For Example:

INSERT INTO default (key, value) values ("k2", {
  "someData" : "someValue",
  "val" : "a2",
  "nested" : [
    {
      "name" : "row1",
      "value" : "a"
    },
    {
      "name" : "row2",
      "value" : "b"
    },
    {
      "name" : "row3",
      "value" : "a"
    },
    {
      "name" : "row4",
      "value" : "c"
    },
    {
      "name" : "row5",
      "value" : "a"
    }
  ]
}) ,
values ("k1", {
  "someData" : "someValue",
  "val" : "a1",
  "nested" : [
    {
      "name" : "row1",
      "value" : "b"
    },
    {
      "name" : "row2",
      "value" : "a"
    }
  ]
})
RETURNING meta().id, *;

UPDATE  default USE KEYS ["k2","k1"] SET nested = ARRAY i FOR i IN nested WHEN i.`value` = "a" END
RETURNING meta().id, *;

Result:
[
  {
    "id": "k2",
    "tmp": {
      "nested": [
        {
          "name": "row1",
          "value": "a"
        },
        {
          "name": "row3",
          "value": "a"
        },
        {
          "name": "row5",
          "value": "a"
        }
      ],
      "someData": "someValue",
      "val": "a2"
    }
  },
  {
    "id": "k1",
    "tmp": {
      "nested": [
        {
          "name": "row2",
          "value": "a"
        }
      ],
      "someData": "someValue",
      "val": "a1"
    }
  }
]
1 Like
3

Thanks a lot. This works exactly as I was searching for.

1 Like