Nested Array Indexing in multiple levels


I need to select documents by property name and value.


SELECT * FROM cms1 where ANY p in props SATISFIES‘str_0’ and (any v in p.values SATISFIES v=‘r0p0[1]’ end) end;


SELECT * FROM cms1 UNNEST cms1.props props WHERE = “str_0” and ANY v in props.values SATISFIES v=“r0p0[1]” END

What is the best array index for these type of queres?

document example

“name”: “res0”,
“type”: “resource”,
“props”: [
“name”: “str_0”,
“values”: [
“name”: “str_2”,
“values”: [


You are doing two variable checks normally array index can store single value.
if your check is equality you can try following.

CREATE INDEX ix1 ON default (DISTINCT ARRAY (DISTINCT ARRAY [,v] FOR v IN p.`values` END) FOR p IN props END);
SELECT * FROM default WHERE ANY p IN props SATISFIES (ANY v IN p.`values` SATISFIES [,v] = ["str_o","repo[0]"] END) END;
SELECT * FROM default d UNNEST d.props prop  WHERE ANY p IN d.props SATISFIES (ANY v IN p.`values` SATISFIES [,v] = ["str_o","repo[0]"] END) END AND = "str_o" AND "repo[0]" IN prop.`values`;
1 Like


Thanks a lot for the quick reply. This is exactly what I need.