UNNEST not picking up the index

SQL++
1

Here is my document structure

{
“type”: “prcsque”,
“queId”: “1234567899”,
“reqId”: “KYCEII01”,
“msgs”: [
{
“msgKeyId”: “UPDATE-ID-DETAILS000000000000003”,
“emailSentIn”: true,
“emailSentTs”: “2018-09-14T14:42:54+05:30”,
“sbnrSLATs”: “2018-09-14T15:42:54+05:30”,
“sbnrStaCd”: “pending”,
},
{
“msgKeyId”: “3174789500AGDMI002”,
“emailSentIn”: true,
“emailSentTs”: “2018-09-14T14:42:54+05:30”,
“sbnrSLATs”: “2018-11-22T03:53:52-07:00”,
“sbnrStaCd”: “completed”,
}
]
}

I have an index defined as

CREATE INDEX bnc_idx3 ON bnc(ALL ARRAY {m.sbnrStaCd, m.sbnrSLATs} FOR m IN msgs END) WHERE type = “prcsque” WITH { “defer_build”:true, “num_replica”:1 };

and another index defined as

CREATE INDEX bnc_type ON bnc(type) WITH { “defer_build”:true, “num_replica”:1 };

However when I execute the following query

SELECT b.queId, m.msgKeyId, m.sbnrStaCd FROM bnc b UNNEST b.msgs m WHERE b.type= “prcsque” AND m.sbnrStaCd = “pending” AND m.sbnrSLATs < “2018-09-14T16:42:54+05:30”

it is not picking up the bnc_idx3 index alone instead it is doing an intersect scan of bnc_idx3 and bnc_type…

May I know the reason for this… And also if there is an alternate way to define the query or the index to avoid intersect index scans

2

Your ARRAY Index key is OBJECT. Query must have object has predicate not individual elements of OBJECT.

CREATE INDEX  `bnc_idx3`  ON  `bnc` (ALL ARRAY [m.sbnrStaCd, m.sbnrSLATs ] FOR m IN msgs END) WHERE type = "prcsque";

SELECT b.queId, m.msgKeyId, m.sbnrStaCd 
FROM bnc AS  b  USE INDEX (bnc_idx3)
UNNEST b.msgs  AS m 
WHERE b.type= "prcsque" AND m.sbnrStaCd = "pending" AND m.sbnrSLATs  < "2018-09-14T16:42:54+05:30"
                   AND [m.sbnrStaCd,m.sbnrSLATs] BETWEEN ["pending"] AND  ["pending", "2018-09-14T16:42:54+05:30"];
3

Nope… thats not working… Can you advise how to redefine the index and the query?

4

Try index and query and previous post