N1QL index selection is based on predicate. If query doesn’t have predicate only option is primary index.
In above query will never choose idx01. It is not an ARRAY index (AS you don’t have DISTINCT or ALL) it just index that transforms OBJECT into ARRAY.
CREATE INDEX idx01 ON ent_comms_tracking(ARRAY { v.name, v.val.data} FOR v IN object_pairs(`values`) END);
SELECT ARRAY { v.name, v.val.data} FOR v IN object_pairs(`values`) END
FROM ent_comms_tracking
WHERE ARRAY { v.name, v.val.data} FOR v IN object_pairs(`values`) END IS NOT NULL;
This above query and index will cover but that type of index doesn’t perform well and no use it almost whole values is part of the index, index will ballon quickly.
Same thing can be achieved by following little better way.
CREATE INDEX idx01 ON ent_comms_tracking(`values`);
SELECT ARRAY { v.name, v.val.data} FOR v IN object_pairs(`values`) END
FROM ent_comms_tracking
WHERE `values` IS NOT NULL;
Array Index will be useful one want to search based on some field. In following case we don’t have array so transforming dynamic object into ARRAY using OBJECT functions and doing Array index.
Example: Want to query if the certain field (dynamically changes each query, “item_1” , “item_10”) in the values is present and get the value of that
CREATE INDEX ix1 ON ent_comms_tracking( DISTINCT ARRAY v.name FOR v IN OBJECT_PAIRS(`values`) END);
SELECT ARRAY v.val.data FOR v IN OBJECT_PAIRS(`values`) WHEN v.name = "item_1" END AS xyz
FROM ent_comms_tracking
WHERE ANY v IN OBJECT_PAIRS(`values`) SATISFIES v.name = "item_1" END;
Would recommend check documentation.
When ever you use OBJECT functions to transform ARRAY then iterate has overhead.
One has to decide data model OBJECT vs ARRAY based on there needs.
Example: OBJECT guarantees uniqueness , doesn’t handle duplicates, easy if know field, search on dynamic field makes difficult,…
ARRAY: handles duplicates, user has to take care of uniqueness, must store name, val as object, known field must iterate through array,…