How to push down multiple Satisfies conditions to the indexer

SQL++
1

Enterprise 6.5

Say I have a doc like this

{
type: ‘thing’
field1: …,
field2: …
arrayfield: [{x:…, y…, z…}, {x:…, y:…, z:…}, …]
}

I want do a query like this:

Select
field1
from default
where type = ‘thing’ and any x in arrayField satisfies x.x = ‘a’ and x.y = ‘b’ and x.z = ‘c’ end

so I make an index like this

(distinct (array x.x for x in arrayField when x.y = ‘b’ and x.z = ‘c’ end), field1) where type = ‘thing’

The above doesn’t hit this index even when I specify it with use index?

Is it possible that the indexer isn’t smart enough to recognize the partiality of the array contents?

If so is there a way to do this?

2 
create index ix21 on default (distinct (array x.x for x in arrayField when x.y = "b" and x.z = "c" end), field1) where type = "thing";
EXPLAIN Select field1 from default where type = "thing" and any x in arrayField satisfies x.x = "a" and x.y = "b" and x.z = "c" end;

Above one uses the index. Once use WHEN clause in the index the values must be same. You can’t change them.

1 Like