Trying to add index for type, so I can search for all lights, as the data structure is not array, we can’t index for devices.type, Is there any way to index and search the type ?
Note: changing the data structure is not an option.
CREATE INDEX ix1 ON default( ALL ARRAY v.type FOR v IN OBJECT_VALUES(devices) END);
SELECT * FROM default d WHERE ANY v IN OBJECT_VALUES(d.devices) SATISFIES v.type = "light" END;
SELECT v FROM default d
UNNEST OBJECT_VALUES(d.devices) AS v
WHERE v.type = "light";
The first query is not working as it will return all devices if one found!
Second one is not acceptable on index performance as it will preform a filter after unfold
If you need matching devices you need to reconstruct object
SELECT OBJECT v.name:v.val FOR v IN OBJECT_PAIRS(d.devices) WHEN v.val.type = "light" END AS devices
FROM default d
WHERE ANY v IN OBJECT_VALUES(d.devices) SATISFIES v.type = "light" END;
Does the index help this query at all ? the explain and plan is telling me this: (in/out)
NOTE: I did search for a key that does not exist at all and it fetch 5 document
index scan */29
distinct 29/5
fetch 5/5
filter 5/0
project 0
stream 0
If you searched that doesn’t exist it should have returned 0/0 in indexscan if the index suggested earlier used.
I am not sure what index is used. If it is primary that means you might not have that many objects that has devices.
I have more than one field in my index, it using other indexes, (they are on meta.id)
CREATE INDEX my_index ON data((split((meta().id), “::”)[0]),(split((meta().id), “::”)[1]),(split((meta().id), “::”)[2]),(all (array (v.type) for v in object_values(devices) end)))
May be leading keys are filtering more and reducing. If the data is like that even you can try this index
CREATE INDEX my_index ON data ((split((meta(). id ), “::”)[0]),(split((meta(). id ), “::”)[1]),(split((meta(). id ), “::”)[2])); – makes index smaller
See what in/out and timing.
Post the query will able to help any other suggestions
I guess I know what happened , I had index on 5 data point, and the type was the fifth one, but in query I forgot to add 4th datapoint in where , so the 5th index was excluded from the index execution plan