Example in array index doc page doesn't hit array index

SQL++
1

I run the same query on the first example listed in this page
http://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

Indexing individual elements in an array

Create an index on all schedules:
CREATE INDEX isched 
        ON `travel-sample` ( DISTINCT ARRAY v FOR v IN schedule END );
The following query finds the list of scheduled 'AA' flights:
 SELECT  * from  `travel-sample`
        WHERE ANY v IN schedule SATISFIES v.flight LIKE 'AA%' END;

However, the SELECT statement still tries to hit primary index

        {
          "#operator": "PrimaryScan",
          "index": "#primary",
          "keyspace": "travel-sample",
          "namespace": "default",
          "using": "gsi"
        },

My couchbase server version is 4.5.0-2601

2

Sorry about that, we will fix the docs. It should say.

CREATE INDEX isched 
        ON `travel-sample` ( DISTINCT ARRAY v.flight FOR v IN schedule END );
3

Thanks for the response. So the filtering statement in collection operation has to exactly match what is indexed?
What if I want to run query and filter on by v.flight and v.day? Do I need to create two indexes, one for v.flight and one for v.day?

SELECT  * from  `travel-sample`
        WHERE ANY v IN schedule SATISFIES v.flight LIKE 'AA%' and v.day = 0 END;
4

Thanks @wxiao and @geraldss. Will fix the docs.

This query should use the index isched. As mentioned in the documentation, the index keys (i.e v.flight) and variables names (i.e v) used in the CREATE INDEX must match those used in the where-clause. However, the where-clause can have additional predicates (such as v.day = 0, in your example).

5

Several options. As @prasad said, you can have additional predicates beyond the index.

Secondly, you can have two array indexes, which will be intersected.

Finally, you can have a single array index with a complex key. For example:

CREATE INDEX complex 
        ON `travel-sample` ( DISTINCT ARRAY [ v.day, v.flight ] FOR v IN schedule END );

EXPLAIN SELECT  * from  `travel-sample`
        WHERE ANY v IN schedule SATISFIES [ v.day, v.flight ] >= [ 0, 'AA' ] AND [ v.day, v.flight ] < [ 0, 'AB' ] END;
1 Like
6

Awesome. The third option meets my need
Thank you @prasad and @geraldss

1 Like