N1QL query - Index Creation

Hi ,
We have a below document and we would like to create an index for this, we want only the active CustRteRltnshps records.

“Id”: “XXXX::1000003_157_01”,
“$Type”: “XXXX”,
“CtryCd”: “mmm”,
“CustRteRltnshps”: [
“CtryCd”: “YYY”,
“RteDocId”: “ZZZZ::YYYYY_XXX”,
“RltnshpTypCdv”: “01”,
“RteID”: “XXXX”,
“EffDtRnge”: {
“EndDt”: “9999-12-25”,
“StrtDt”: “2012-03-11”

Our Current query is,
select a.CtryCd, rte.RteID, rte.CtryCd as rtctrycd, rte.EffDtRnge.StrtDt, rte.EffDtRnge.EndDt FROM masterdata-target a unnest CustRteRltnshps rte
WHERE a.$Type = ‘Customer’
AND rte.EffDtRnge.StrtDt <= CLOCK_LOCAL()
AND rte.EffDtRnge.EndDt >= CLOCK_LOCAL()

Explain gives only $Type index creation. Any possible solution?


“Explain” tells you what index(es) the query is using, it doesn’t tell you what index you should create. “Advise” is probably what you are looking for. For example, when I run “Advise” on that query in 6.6 Enterprise (or via the web-based query advisor), it recommends:

Covering Index Recommendations

CREATE INDEX adv_ALL_CustRteRltnshps_Type_CtryCd ON `masterdata-target` (ALL `CustRteRltnshps`,`$Type`,`CtryCd`)

Index Recommendations

CREATE INDEX adv_ALL_CustRteRltnshps_Type ON `masterdata-target`(ALL `CustRteRltnshps`,`$Type`)