Update JsonArray document

SQL++
1

Hello,
I have a document (not a field in a document) like this
[ "foo", "bar" ]

Can I add/remove a value to the document using UPDATE - WHERE, without a sub-query.

Pseudo:
update default use keys "arr_key" add "baz" where "baz" not in document

2

Hello, yes.

UPDATE default d
USE KEYS "arr_key"
SET d = ARRAY_APPEND(d, "baz")
WHERE "baz" NOT IN d;
3

Hi Gerald,
Thanks for the answer. I’ve already tried this beforehand and I’ve just tested it again. Unfortunately it doesn’t work.

Here is the log:

cbq> select * from default use keys “arr_key”;
{
“requestID”: “34366cc4-0110-456d-a2a9-b2f6987722cc”,
“signature”: {
"": ""
},
“results”: [
{
“default”: [
“foo”,
“bar”
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “1.763941ms”,
“executionTime”: “1.688594ms”,
“resultCount”: 1,
“resultSize”: 95
}
}

cbq> UPDATE default d USE KEYS “arr_key” SET d = ARRAY_APPEND(d, “baz”) WHERE “baz” NOT IN d;
{
“requestID”: “a19146f4-ef8d-4119-bb3a-cb2a0c91ba71”,
“signature”: null,
“results”: [
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “3.516254ms”,
“executionTime”: “3.461345ms”,
“resultCount”: 0,
“resultSize”: 0
}
}

cbq> select * from default use keys “arr_key”;
{
“requestID”: “05f21529-7099-4e29-acbf-4c368b558f37”,
“signature”: {
"": ""
},
“results”: [
{
“default”: [
“foo”,
“bar”
]
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “2.752822ms”,
“executionTime”: “2.678372ms”,
“resultCount”: 1,
“resultSize”: 95
}
}

Version: 4.0.0-4051 Community Edition (build-4051)

4

Use the following for your SELECTs:

SELECT d FROM default d USE KEYS ...;

5

The issue is not with the SELECT, I can see that the document isn’t updated through the GUI. I think the update query is not correct.

Seems like we’re missing the reference to the top level document content in the set clause;

For example if I add a RETURNING d at the end

update default d use keys “arr_key” set d = array_append(d, “baz”) returning d;
{
“requestID”: “e19553f0-b8e7-4fb7-b90d-a7e06d8d5759”,
“signature”: {
“d”: “json”
},
“results”: [
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “1.592525ms”,
“executionTime”: “1.540968ms”,
“resultCount”: 0,
“resultSize”: 0
}
}

6

Okay, I have a working version of the query. I had to reference the array index explicitly for it to work.
Luckily we can count it on execution.

update default d use keys "arr_key" set d[ARRAY_LENGTH(d)] = "baz" where "baz" not in d;

7

Thank you. That might be a bug in our implementation.