Couchbase 4.5.1 secondary indexes for N1QL

SQL++
, ,
1

I’m restricted to using 4.5.1 for now and new to Couchbase N1QL but am having a problem getting the following query to work without a Primary Index. I want to use secondary indexes but am not sure what indexes I should create for this statement to work.

select CODE, CODE_NAME, PRIMARY, SNUM, SHORTENED_NAME, TYPE
from default where UPPER(CODE) like ‘P%’ or UPPER(SNUM) like ‘P%’ or UPPER(CODE_NAME) like ‘P%’
or UPPER(SHORTENED_NAME) like ‘P%’

Any suggestions are appreciated

2

It looks like you want match same value present in any one of the filed.
You can try the following ARRAY indexing technique. Make sure variable names in index and ANY clause of query same for pre 4.6.2

CREATE INDEX ix1 ON default (DISTINCT ARRAY UPPER(v) FOR v IN [CODE,SNUM,CODE_NAME, SHORTENED_NAME] END);

SELECT  ...
FROM default 
WHERE ANY v IN  [CODE,SNUM,CODE_NAME, SHORTENED_NAME] SATISFIES UPPER(v) LIKE "P%" END;