Optimize N1ql query to match embedded array elements

SQL++
,
1

Hi,

I have documents like below:
{
“type”: “tp1”,
“colors”: [
“Blue:10”,
“Yellow:20”,
“Green:*”,
],
}
The elements in the colors array are not fixed and may increase or decrease.

I am creating below N1QL query:

SELECT b1.* from bucket as b1 where type = “tp1” AND (ARRAY_CONTAINS(b1.colors, “Blue:10”) OR ARRAY_CONTAINS(b1.colors, “Blue:")) AND (ARRAY_CONTAINS(b1.colors, “Yellow:10”) OR ARRAY_CONTAINS(b1.colors, "Yellow:”)) AND (ARRAY_CONTAINS(b1.colors, “Green:10”) OR ARRAY_CONTAINS(b1.colors, “Green:*”))

The index created for this is:
CREATE INDEX idx ON bucket(colors) WHERE (type = “tp1”)

Couchbase version 6.5.1

This query is slow and I want to know if there is a faster way to get the documents?
In addition, I have the privilege of modifying ‘colors’ array to optimize performance. Can you suggest?

1 Like