Using N1QL reserved words

1
votes

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'
2
couchbasen1ql
Please post the sample document - vsr

2 Answers

0
votes

You're on the right track with backticks to escape reserved words.

If you aren't getting an error message, then I suspect it's your WHERE clause. Are you sure your documents have an "id" field in them?

{ "id": "12345678Z", "foo": "bar", ... etc ... }

Or are you trying to query based on the document ID (in the below screenshot there is no "id" field in the document)?

enter image description here

If the latter, then you should use META().id:

SELECT * FROM `testbucket` r where META(r).id = '12345678Z'

I know this might seem a bit confusing, but "id" isn't a magic field in Couchbase. It's a field just like any other. The actual ID of the document is in the document's metadata.

Also, a recommendation: if you are trying to get a single document by its ID, you're much better off using the key/value API instead of a N1QL query. No need to involve the overhead of query/index services.

0
votes

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;