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

SQL++
1

Hi,

I’m trying to create a covered index using some data from an object, but with unknown key names. I’m not able to figure out how to get the query planner match and get the data back. This is example data (insert multiple times with different key names if you want):

INSERT INTO `test-bucket` ( KEY, VALUE )
  VALUES
  (
    "k001",
    {
  "id": "1da0c06c-b9af-404c-ab51-86f1b00f7c0e",
  "stuff": {
    "25443790-6ab9-4374-90f2-aa927b066ae8": {
      "thing": "one",
      "nested": { "foo": "bar" }
      "huge": "object"
    },
    "35fd7f18-a3ba-4913-940d-d0c17769a8de": {
      "thing": "two",
      "nested": { "foo": "bar" }
      "huge": "object"
    }
  }
}


  )
RETURNING META().id as docid, *;

And I’ve created an index like this:

CREATE INDEX `test_idx0_v1`
ON `test-bucket`((distinct (array `l` for `l` in object_names(`stuff`) end)),`stuff`, `id`)

And then to select data, something like this:

SELECT meta(t).id, stuff FROM `test-bucket` t  
WHERE ANY l IN OBJECT_NAMES(t.stuff) SATISFIES l IN ['25443790-6ab9-4374-90f2-aa927b066ae8', '35fd7f18-a3ba-4913-940d-d0c17769a8d'] END

BUT… what I really want to do is be able to avoid dropping the “huge”:“object” portion of the hash into the index. Once I try and refactor the above to only put “thing”:“one” and “thing”:“two” and “nested” object into the index, which I can with an multi-value array index, I can’t figure out how to match on only one of the keys.

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

Now I have an index exactly as I want it. I have each key from stuff as the first entry in the index, what I really want to match against, and all the other data I need for it to be a covered query and not fetch any documents. But I can’t figure out how to select against it and have the planner know, without matching everything! For example:

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', 'two', 'bar'] END

Which works! But I don’t know ahead of time what thing and foo are. I actually just want that as covered data in my select. But the only way I’ve found to even get the query planner to match this is:

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', l.val.thing, l.val.nested.foo] END

The two problems here is that not only does this result in a fetch, I really only care about selecting against the first element in the array (the uuid, l.name) and selecting the second and third element in the array, not matching against it. And ideally that first element in the array needs to be an IN match like my first example, not equals. I want to create the index without the huge amount of data that’s in the object, but on an unknown number of keys, select the values from them, matching only on the uuid and not the rest of the array. Is something like this possible?

TL;DR, I want certain parts of a unknown name and number of nested objects covered and selectable.

Thanks,
Shane

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
3

This works! Thank you very much!