Need help to create Array Index on SELECT with UNNEST

SQL++
1

Hi,

I have following N1QL:

select temp.num as number, grp_ids as numberGrpId
from (select meta().id AS num, grp_ids from config where svcname =“rsc” and __t =“rsc-number-group”
and status != 2) temp
UNNEST temp.grp_ids

The field “grp_ids” is an array.
Currently I am using a simple index:
CREATE INDEX ind_config_type ON config(__t)
However I am achieving very huge response time.

Since there is a sub-query, I am unable to figure out a way to create an array index to optimize the response time. Can someone guide me on how to create array index on this query ?

Thanks in advance.

2

I tried creating following index which is not getting used.
create index pratiktest1 on config((all (array v for v in grp_ids end)),status) where svcname =“rsc” and __t =“rsc-number-group” and status != 2;

So when I use USE INDEX, I get following error! :frowning:

{
“code”: 3000,
“msg”: “FROM Subquery cannot have USE KEYS or USE INDEX or join hint (USE HASH or USE NL). - at UNNEST”,
“query_from_user”: “select temp.num as number, grp_ids as numberGrpId\r\nfrom (select meta().id AS num, grp_ids from config where svcname =“rsc” and __t =“rsc-number-group”\r\nand status != 2) temp USE INDEX (pratiktest1)\r\nUNNEST temp.grp_ids”
}

3

There is no predicate on ARRAY elements. So there will not be any benefit of array index.
You can use following query and index. (If grp_ids is too big remove from index. It will do fetch)

SELECT META(c).id AS num, gid AS numberGrpId
FROM config AS c
UNNEST c.grp_ids AS gid
WHERE c.svcname = "rsc" AND c.__t = "rsc-number-group" AND c.status != 2;

CREATE INDEX ix1 ON config(svcname, __t, status, grp_ids);