Index for the intersection of a sub array and a static array as a parameter

N1QL
, ,
#1

I have a expression in a where clause that looks like: and ARRAY_LENGTH(ARRAY_INTERSECT([“900”,“400”], r3.groupTags)) > 0

r3 is an unnested item from an array of objects
is there a way to get an index to hit on that expression or something equivalent?

#2

Is it possible to post sample document and query.

This is equivalent of .

ANY v IN r3.groupTags SATISIFIES v IN ["900","400"] END

If groupTags is part of document you can create ARRAY Index

#3

Thank you! That did it. It was tricky because it was also nested. Here is the new index:

Create Index idx_gt on app
(Distinct Array 
    (Distinct Array gt for gt in r.groupTags END)
    FOR r in ruleVersions.__main__ end)
    
    where type = "rule"

Here is the query that uses it. The stuff is bold is what I added

select  raw maxAppVer.theRule
from app rules
let maxAppVer = 
    (select max(maxRule.version) as ver, maxRule as theRule
    from rules.ruleVersions.__main__ as maxRule
    let minApp = 
        (select min(minRule.maxDischargeDate) as minDate 
        from rules.ruleVersions.__main__ as minRule
        where minRule.maxDischargeDate >= "2017-05-21T01:00:00-07:00"
        and minRule.state != 500 and minRule.state != 5000)[0]
    where maxRule.maxDischargeDate = minApp.minDate
    and maxRule.state != 500 and maxRule.state != 5000
    and ARRAY_LENGTH(ARRAY_INTERSECT(["001","*"], maxRule.groupTags)) > 0
    group by maxRule 
    )[0]
where rules.type = "rule"
and maxAppVer.ver is not null
**and any r in rules.ruleVersions.__main__ SATISFIES  **

** (any gt in r.groupTags SATISFIES gt in [“001”,"*"] end)**
** end**

#4

Hope your question is answered.

#5

It was thanks. That was the hint I needed

#6

Any tips on how to modify the index to make it a covering index?

#7

Include ruleVersions.__main__ as trailing key

#8

That worked. cut execution time in half. Thanks!
Question: How does one know how to order the key parts in a index definition? Is there a write up on that somewhere?

#9

Indexer uses B-tree .Check this out N1ql indexing sequence

Useful N1QL articles

#10

@DrGarbinsky, The following might be useful for you based on above query. While doing MIN/MAX you can project what ever you want.

Example:   MAX( [ maxRule.version, maxRule] )[1]  AS theRule
                  Max argument is ARRAY of First element is expression on what you want to do MAX
                                   Second element is expression what you want project or carry with MAX value.
                   Final result [0] gives MAX value, [1] gives  what  you want project