I have CTE based SUBQUERY block named “sq_filter” which returns below kind of jsons
{
"aId": "2769ba8f",
"bId": "5837c902",
"tn": "oi"
}
I want to update collection named “xyz” containing below 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
Below 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.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 below syntax error
syntax error - line **, column 1, at: UPDATE (reserved word)