couchbasesql++

Couchbase: syntax error. Not accepting NOT IN array

Problem Description

I have the following query.

SELECT ARRAY_REVERSE(SPLIT(META().id, ":"))[0] AS _id not in ["1-200101", "2-200101"]
FROM base
WHERE SUBSTR0(META().id, 0, 2) = "x:"
    AND code = "XXX"
    AND indexed_data.type = "Fire" 
    AND (indexed_data.link IS VALUED AND indexed_data.linkin ["3-200101"])
    AND (indexed_data.shared IS VALUED AND indexed_data.shared in ["4-200101"]) 
    LIMIT 998

Couchbase is not accepting not in ["1-200101", "2-200101"] I can't find a solution.

Error

  [
  {
    "code": 3000,
    "msg": "syntax error - at not",
    "query": "SELECT ARRAY_REVERSE(SPLIT(META().id, \":\"))[0] AS _id not in [\"1-200101\", \"2-200101\"]\r\nFROM base\r\nWHERE SUBSTR0(META().id, 0, 2) = \"x:\"\r\n    AND code = \"XXX\"\r\n    AND indexed_data.type = \"Fire\" \r\n    AND (indexed_data.link IS VALUED AND indexed_data.linkin [\"3-200101\"])\r\n    AND (indexed_data.shared IS VALUED AND indexed_data.shared in [\"4-200101\"]) \r\n    LIMIT 998"
  }
]
Solution

  • Once aliased projection expression (AS _id) after that must have comma and another projection or FROM.

    IN can be used any where expression is allowed

    SELECT _id
FROM base
LET _id = SPLIT(META().id, ":")[-1]
WHERE META().id LIKE "x:%"
    AND code = "XXX"
    AND indexed_data.type = "Fire"
    AND indexed_data.link IN ["3-200101"]
    AND indexed_data.shared IN ["4-200101"]
    AND _id NOT IN ["1-200101", "2-200101"]
LIMIT 998

    NOTE: f1 = 10 In predicate already implied (f1 IS VALUED AND f1 = 10). Negative array subscript means index from the end.