Index on Array not extracting correct information

SQL++
1

Hi have bucket1.scope1.collection1 with several documents that are defined like below one:

{
“id”: “12345”,
“schedule”: [
{
“id1”: “1111111”
“day”: 3
},
{
“id1”: “2222222”
“day”: 4
}
],
“type”: “plan”
}

I have created an index based on the “schedule” array data as per below:
CREATE INDEX idx_distinct_array ON bucket1.scope1.collection1(DISTINCT ARRAY v.id1 FOR v in schedule END) WHERE type = “plan”

Whenever I run below Query, I do not get any error due to missing index, but I get empty results, and do not understand why I do not get any record at all on the select.

SELECT * FROM bucket1.scope1.collection1 WHERE (ANY v IN ‘schedule’ SATISFIES v.id1 = “1111111” END) AND collection1.type=“plan”;

What is wrong in my setup? Am I missing something?
The way couchbase is working with the indexes is a bit tricky for me, so maybe I did something wrong and did not realize of that.

Thanks for your help!

2

It appears you have single-quotes around ‘schedule’ in your statement. It can be unquoted or in grave accent quotes (back quotes): `schedule`.
HTH.

1 Like
3

HI,

It did the trick!!
I had not realized of that since I did not get any syntax error at all.
Thanks a lot!!

4

Glad it worked.
You won’t have had a syntax error since we do permit a literal there; if the type is incorrect (in this case it was a string rather than an array) NULL is the result rather than an error (this tends to be because schema-less design may mean varying types - e.g. one document a field is an array and for another it is an integer).