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

https://www.couchbase.com/forums/t/how-to-fix-couchbase-update-sql-with-cte-which-is-giving-error/38748