Creating a covered index with array, how to access array elements in select?

SQL++
2

You are in the right track.
Try the following query on your index.

CREATE INDEX `test_idx0_v2`
ON `test-bucket`((distinct array [l.name, l.val.thing, l.val.nested.foo] for `l` in object_pairs(`stuff`) end), `id`);

SELECT meta(t).id FROM `test-bucket` t  
WHERE ANY l IN OBJECT_PAIRS(t.stuff)
 SATISFIES [l.name, l.val.thing, l.val.nested.foo]  >= ['35fd7f18-a3ba-4913-940d-d0c17769a8de']
                         AND [l.name, l.val.thing, l.val.nested.foo]   < [SUCCESSOR( '35fd7f18-a3ba-4913-940d-d0c17769a8de') END;

In array comparison of equality is easy when you have all the values. In your case you know the name but not other two values. If you know only first value use lessthanequal and graterthan with next value (SUCCESSOR() gives next value). As right side you gave one value only it matches all possible value in the position 1, 2.
This very complex operation use with caution otherwise result in wrong results. You can’t skip the position of array and apply next value.

Array comparison does from left to right, it moves next position when previous position is equal, if not equal the condition is already decided (true or false based on value). NOTE: You can’t apply LIKE predicates.

If you want apply name , foo as predicates you can try this.
Use ALL keyword in the index, UNNEST alias and index binding variable must same( i.e. l). Refer every where with [l.name, l.val.thing, l.val.nested.foo] and if need specific element use subscript.
Below case index scan done on name and foo filter apply post index scan but still uses cover.

CREATE INDEX `ix1`
    ON `test-bucket`(ALL ARRAY [l.name, l.val.thing, l.val.nested.foo] FOR `l` IN OBJECT_PAIRS(`stuff`) END), `id`);

SELECT  meta(t).id,  [l.name, l.val.thing, l.val.nested.foo][0] AS name,  [l.name, l.val.thing, l.val.nested.foo][2]  foo
FROM `test-bucket` t
UNNEST OBJECT_PAIRS(t.stuff) AS l
WHERE [l.name, l.val.thing, l.val.nested.foo]  >= ['35fd7f18-a3ba-4913-940d-d0c17769a8de']
      AND [l.name, l.val.thing, l.val.nested.foo] < [SUCCESSOR( '35fd7f18-a3ba-4913-940d-d0c17769a8de')]
      [l.name, l.val.thing, l.val.nested.foo][2] = "foovalue";

SELECT  meta(t).id,  nv[0] AS name,  nv[2] AS foo
FROM `test-bucket` t
UNNEST OBJECT_PAIRS(t.stuff) AS l
LET nv = [l.name, l.val.thing, l.val.nested.foo]
WHERE nv  >= ['35fd7f18-a3ba-4913-940d-d0c17769a8de']
      AND nv < [SUCCESSOR( '35fd7f18-a3ba-4913-940d-d0c17769a8de')]
      nv[2] = "foovalue";
1 Like