N1QL Array Indexing

SQL++
1

Hi,

trying to create array index for the following scenario,
array will be holding list of holidays from start date to end date and reason it is on holiday.
but following query doesn’t use the array index, any idea…what is the issue here?.
e.g
—array on stop doc.
stopholiday[
[“JAN-01-18”,
“JAN-01-18”,
“NEW YEAR”
]
[“JAN-19-18”,
“JAN-19-18”,
“NEW YEAR”
]
[“MAY-28-18”,
“MAY-28-18”,
“LABOUR DAY”
]
]

====index script.
CREATE INDEX d_stopholiday ON dss((distinct (array v for v in stopholiday end))) WHERE ((type = “holiday”);

explain select meta(hld).id
from sholiday hld
where type = “holiday”
and ANY v IN ARRAY_RANGE(0,ARRAY_LENGTH(hld.stopholiday)) SATISFIES hld.stopholiday[0][v] =“2018-02-13” END
and ((hld.type = “holiday”) and meta(hld).id=“holiday::2323232”;—document key
i assuming my index scripts needs to corrected.

2

Array Indexing : https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/indexing-arrays.html

Use dates as ISO860-1 format so that you can use date functions . https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/datefun.html

The following query and index uses array indexing

INSERT INTO default VALUES ("ff01",{ "stopholiday":[ ["2018-01-01", "2018-01-01","NEW YEAR" ], ["2018-01-19", "2018-01-19","NEW YEAR" ], ["2018-05-28", "2018-05-28","MEMORIAL DAY"] ], "type" : "holiday"});

    CREATE INDEX ix1 ON default (DISTINCT ARRAY ( DISTINCT ARRAY hd FOR hd IN DATE_RANGE_STR(sh[0], DATE_ADD_STR(sh[1],1,"day"), "day") END)
                                          FOR sh IN stopholiday END) WHERE type = "holiday";

    SELECT META(d).id
    FROM default AS d
    WHERE d.type = "holiday" AND ANY sh IN d.stopholiday SATISFIES (ANY hd IN DATE_RANGE_STR(sh[0], DATE_ADD_STR(sh[1],1,"day"), "day")
                                 SATISFIES hd = "2018-05-28" END) END;

If predicate has meta(hld).id=“holiday::2323232” you no need array index, you can try the following query (“ff01” is document key)

SELECT META(d).id
FROM default AS d USE KEYS "ff01"
WHERE d.type = "holiday" AND ANY sh IN d.stopholiday SATISFIES (ANY hd IN DATE_RANGE_STR(sh[0], DATE_ADD_STR(sh[1],1,"day"), "day")
                             SATISFIES hd = "2018-05-28" END) END;

OR

 SELECT META(d).id
 FROM default AS d USE KEYS "ff01"
 WHERE d.type = "holiday" AND ANY sh IN d.stopholiday SATISFIES "2018-05-28" BETWEEN sh[0] AND sh[1] END;
3

Thanks for your inputs…will try out and let you know, for any issues.