Search code examples
couchbasesql++

Using N1QL reserved words

I have a document with the structure { doc.data.role.level }, I want to access to level property, but using this query

SELECT r.data.role.level    FROM `hostel` r where r.id = '12345678Z'

I got the error

"msg": "syntax error - at role",

but with those query I got no results

SELECT r.data.`role`.level  FROM `hostel` r where r.id = '12345678Z'
SELECT `r.data.role.level`  FROM `hostel` r where r.id = '12345678Z'
Solution

  • If there are special characters in fields/identifiers or reserve keywords you must escape them with back-ticks(i.e. escaped identifiers) as described https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/identifiers.html

    Example 1: "role" is reserve keyword

    {
 "id":"12345678Z",
 "data":{"role":{"level":3}}
}

SELECT r.data.`role`.level
FROM hostel AS r
WHERE r.id = "12345678Z";

    Example 2: Field has special character dot. You must escape whole field name (to interpret as field vs nested document).

    {
 "id":"12345678Z",
 "data.role.level":5
}

SELECT r.`data.role.level`
FROM hostel AS r
WHERE r.id = "12345678Z";

    Example 3: hostels is array. If you are looking inside array you must use ANY syntax.

    {
 "hostels": [ { "id":"12345678Z"},
              { "id":"34545678Z"}}
            ]
 "data":{"role":{"level":3}}
}

SELECT r.data.`role`.level
FROM hostel AS r
WHERE ANY h IN r.hostels SATISFIES h.id = "12345678Z" END;