Index for 2 array operation in single N1QL query

Hi,

I’m creating below N1QL query -

SELECT b1.bkey AS bkey FROM f AS b1
WHERE ( EVERY v IN b1.f1 SATISFIES LOWER(v) != ‘if_70’ END AND EVERY v IN b1.f2 SATISFIES LOWER(v) != ‘if_69’ END )
OR
( EVERY v IN b1.f1 SATISFIES LOWER(v) != ‘if_70’ END AND ANY v IN b1.f2 SATISFIES LOWER(v) = [“if_70”,“if_69”] END )

below is Index Advise of above query -

Index Recommendations

CREATE INDEX adv_DISTINCT_f2_lower ON f(DISTINCT ARRAY lower(v) FOR v in f2 END)

Couchbase version - 6.5.1
On creating Primary Index query works fine but with Advise recommended Index query still not working.

I tried more combination of Indexes but none of worked with the above query.
Please suggest feasible index for the query.

Thanks,
Naveen

EVERY clause will not able to use any index. Only option is primary index.

The recommendation advise might be second part of OR due to ANY clause. As there is no index for first part of OR, there will not be any secondary index whole query.

1 Like

Thanks, I found below as alternate of EVERY clause -

select ((ANY AND EVERY v IN [] SATISFIES v > 0 END) OR ARRAY_LENGTH([]) = 0 ) as any_every_res, (EVERY v IN [] SATISFIES v > 0 END) as every_res