How to write Where condition on arrays using INDEX

SQL++
1

While trying the N1QL language, I found a difficulty to request the following:

Let say the document contains, among other attributes, an array of values as in
varArray : [ val1, val2, val3 ]

How can I make the WHERE condition such that the SELECT returns the document containing such an attribute varArray with at least one value being val2 ?

Even if not explained in the N1QL language, it seems that it could be
WHERE val1 IN varArray

Is that the correct way? Is there other way ?

I’m asking this too because when I add an INDEX on this varArray by:
CREATE INDEX varArrayIndex ON MyBucket(varArray)
And then executing the SELECT with EXPLAIN, I saw that it does not take into account the index.
Obviously when I got more than 1 millions of element, the request takes very long (full scan) while it should be quick if the index was used.

Am I missing something ?
Note: I’m using the last available versions of Couchbase and Java SDK and N1QL binaries at the time I wrote this question.

Thanks,
Frederic

2

Hi Frederic,

You should be using N1QL DP3 currently. Your syntax is correct:

WHERE val1 IN varArray

For more complex conditions, see the following syntax in the docs and tutorial:

WHERE ANY v IN varArray SATISFIES END

As for indexing on array elements, it is currently not available. We will look into it:

http://www.couchbase.com/issues/browse/MB-11677

Thanks.

3

Thank you for your feedback !
Concerning the ANY IN SATISFIES, I tried but it always returns a syntax error.

I tried something like:
SELECT * FROM mybucket WHERE ANY val IN varArray SATISFIES varArray=val1
But I think this is wrong since the varArray is a “simple” array (array of simple values, as integer or string) and not an array of Json element (as varArrayJson : [ { var : val1 }, { var: val2 }, { var : val3 } ] ).

So I believe that ANY expression is not valid for such “simple” arrays, right ?

Thanks

4

Try

SELECT * FROM mybucket WHERE ANY val IN varArray SATISFIES val=5 END