I have a CTE based SUBQUERY block named sq_filter
which returns this kind of JSON:
{
"aId": "2769ba8f",
"bId": "5837c902",
"tn": "oi"
}
I want to update collection xyz
containing this kind of documents:
{
"blocks": {
"down": [
{
"dId": "bd8e20c9",
"cti": "dd",
"len": 111
}
],
"up": [
{
"bId": "5837c902",
"cti": "bb"
},
{
"bId": "d322701d",
"cti": "bb"
}
]
},
"aId": "2769ba8f",
"cti": "aa",
"tn": "oi"
}
Given above two items, I want to write update query for collection xyz
which finds matching "xyz"."tn" = "sq_filter"."tn"
and "xyz"."aId" = "sq_filter"."aId"
from collection xyz
, and remove matching "sq_filter"."bId"
from "blocks"."up"
array.
This is my query:
WITH abc AS (
...
),
sq_filter AS (
...
)
UPDATE xyz AS x
SET x.blocks.up = ARRAY v FOR v IN x.blocks.up WHEN v.bId != (SELECT sf.bId FROM sq_filter AS sf WHERE x.tn = sf.tn AND x.aId = sf.aId) END
WHERE x.aId = sq_filter.aId
AND x.tn = sq_filter.tn
AND x.cti = "aa";
Expectation post update of said document from "xyz" collection, which is removal of one element from "blocks"."up" array which is matching sq_filter's bId
{
"blocks": {
"down": [
{
"dId": "bd8e20c9",
"cti": "dd",
"len": 111
}
],
"up": [
{
"bId": "d322701d",
"cti": "bb"
}
]
},
"aId": "2769ba8f",
"cti": "aa",
"tn": "oi"
}
I am getting a syntax error:
Syntax error - line **, column 1, at: UPDATE (reserved word)
Link
Use Merge statement https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/merge.html You may have to create right index on target table
MERGE INTO xyz AS x
USING (WITH abc AS ( ...)
SELECT .... ) AS sq_filter
ON x.aId = sq_filter.aId
AND x.tn = sq_filter.tn
AND x.cti = "aa"
WHEN MATCHED THEN UPDATE SET x.blocks.up = ARRAY v FOR v IN x.blocks.up WHEN v.bId != sq_filter.bId END;