Query to match an Array of objects based on range

Hi,

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

I need to select the documents based on the range for the colors, e.g Blue = 19, Red = 25, Yellow =16, as these are in the range from and to in the above document, the above document should be retrieved.

Could you please suggest the index required to be created for this and the N1QL that can be used?
The Couchbase version is 6.5.1

1 Like

We can change the document to have the Json as below:
{
“type”: “tp1”,
“colors”: [
{"name “:“Blue”,
“from”:10,
“to”:20
},
{"name “:“Red”,
“from”:20,
“to”:30
},
{"name “:“Yellow”,
“from”:15,
“to”:20
}
]
}

Please suggest according to this.

1 Like
SELECT d.*
FROM default AS d
WHERE d.type = "tp1"
AND ANY v IN d.colors SATISFIES v.name =  "Blue" AND v.`from` <= 19 AND  v.`to` >=19 END
AND ANY v IN d.colors SATISFIES v.name = "Red" AND v.`from` <= 25 AND  v.`to` >=25 END
AND ANY v IN d.colors SATISFIES v.name =  "Yellow" AND v.`from` <= 16 AND  v.`to` >=16 END;

https://index-advisor.couchbase.com/indexadvisor/#1