Create GSI for indexing the string in array

SQL++
,
1

I have an issue regarding of the creating GSI for array,
right now, I have those array in my document:

{'tags':['tag1','tag2']}

and I want use this query to search all of the documents contains one of the string in this array:

SELECT * FROMBucket_nameWHERE _type='Test' AND ANY t in tags SATISFIES t='Tag1' END;

To enhance the performance, I want to use the GSI:

CREATE INDEX Test_index_Tag ONBucket_name(tags) WHERE _type='Test' USING GSI;

But after I created this index, the query execution time still the same. any ides?

2

Hi there @zzkkyy1000, at the moment, when we index tag in GSI we are indexing the full array so you can use the index for a query like the following which would search for an exact match to the array.

SELECT * FROM Bucket_name WHERE _type=‘Test’ AND tags=[‘tag1’,‘tag2’];

We are building a new capability that will iterate over the array elements and optimize the ANY t IN tags clause you have above. If you’d like to get a preview of plans on the new array indexes, feel free to reach out at cihan@couchbase.com and I 'd be happy to walk you through the details.

thanks
-cihan

4

got it, thank you very much! looking forward to the new features

5

is there any references how to use array indexing in the preview? thanks a lot

6

Here is links to documentation and blogs on array indexing in 4.5:

http://blog.couchbase.com/2016/march/making-the-most-of-your-arrays…-with-array-indexing

http://blog.couchbase.com/2016/may/1.making-most-of-your-arrays…-with-covering-array-indexes-and-more

http://developer.couchbase.com/documentation/server/4.5/n1ql/n1ql-language-reference/indexing-arrays.html

Happy array indexing !!
-Prasad

1 Like