SELECT on complex structures do not seem to work

SQL++
1

My documents look like this:

wi_rl : {

lang: "de-DE",
"druck-num": "K00.5921.11.14",
vorspann: {
...
}
...

}

If I do the following SELECT from cbq I always get an empty result set.
SELECT wi_rl.lang FROM elsa;

I also tried
SELECT lang FROM elsa;

I always get an empty result set.

If I do
SELECT COUNT(*) FROM elsa;

then I get the correct result stating that there are 30 documents in the bucket.

In the Couchbase console I can see the documents and they also have the structure shown above.

2

you first SELECT has the correct syntax. Can you ensure attribute are in double-quotes:

“wi_rl” : {
“lang”: “de-DE”,
“druck-num”: “K00.5921.11.14”,
“vorspann”: {
…
}
…
}

if this does not work, I think this may be due to the fact that the value isn;t encoded correctly in Couchbase Server. Can you go under “Data Buckets” in the admin tool and click on “Documents” button next to bucket “Elsa” > type in the document ID and ensure Couchbase has received the JSON and can display it correctly?

thanks
-cihan

3

The attributes are in double quotes.I only forgot to put them into double quotes when writing this blog. I can see the correct JSON structure in the Couchbase Admin console, if I go to the primary view and select show.
Even, if I do SELECT * from elsa I get an empty result set.

4

Can you also confirm you see the values correctly encoded in the admin portal under the data buckets tab?

5

If I go to the data buckets tab, I can see that there are 30 documents in the bucket. If I select the documents button next to the bucket then the console starts loading but does not finish loading. After a timeout I have to close the window.

I also created the following view now:
function (doc, meta) {

if (doc.wi_rl.lang)
emit(meta.id, doc.wi_rl.lang);

}

With that view I get a result set of 30 documents in the admin console.

6

I need 2 things;

  • could you send me the app you used to create the items. I’ll try this on my end.
  • could you also emit meta.flags on the view and send me the output with the 30 items?
    thanks
    -cihan
7

Now I got it working. I deleted the primary index and recreated it. After that I get the expected results when submitting SELECT statements.

8

That is very interesting. Did you use view or gsi for primary index?

9

I created the primary index from cbq with CREATE PRIMARY INDEX on elsa. The same I did already before importing the documents on the empty bucket. But obviously there seemed to be a problem with the primary index after importing the documents. Now when I recreated the primary index I can also see the list of documents in the data buckets tab of the admin console although it takes quite a long time until the list is generated.

10

I found another problem concerning attributes containing a dash in the attribute name. If I submit for example SELECT wi_rl[“dok-id”] FROM elsa I get a result set with 30 null values although every document has a dok-id.

11

the dash needs to be escaped. try > SELECT wi_rl.‘dok-id’ FROM …

’ above represent the back-tick which is the escapa character for N1QL.
Edit: Corrected to visualize ticks now.

12

Yes, now it works. I could also submit queries on deeply nested arrays and objects. N1QL is really great! That’s what I need.

1 Like