Is it possible to index a key inside value of a hashmap/object?

SQL++
1

I have a data structure like this:

{
  "devices": {
    "device_111": {
      "id": "11",
      "type": "light",
      "name": "device_name_11"
    },
    "device_222": {
      "id": "22",
      "type": "door",
      "name": "device_name_22"
    },
    "device_555": {
      "id": "55",
      "type": "light",
      "name": "device_name_55"
    },
    .
    .
    .
 }

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.

2

Use OBJECT functions with array index https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/objectfun.html

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";
3

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

4

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;
6

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

7

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.

8

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)))

9

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

10

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