Nested Array Indexing in multiple levels

SQL++
1

Hi,

I need to select documents by property name and value.

variant1

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

variant2

SELECT * FROM cms1 UNNEST cms1.props props WHERE props.name = “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”: [
“r0p0[0]”,
“r0p0[1]”,
“r0p0[2]”
]
},
{
“name”: “str_2”,
“values”: [
“r0p2[0]”,
“r0p2[1]”,
“r0p2[2]”
]
}
]
}

Thanks

2

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 [p.name,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 [p.name,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 [p.name,v] = ["str_o","repo[0]"] END) END AND prop.name = "str_o" AND "repo[0]" IN prop.`values`;
1 Like
3

@vsr1

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