How to create a array index on an array of objects

SQL++
1

Let’s say I have the following object inside the database:

{a: {a: 4, c: “test”}, b: {a: 7, c: “something”}}

and query it using the given query:

Select * from db where any v in object_values(v) satisfies v.a > 4 and v.c = “test” end

How would I go about creating a index for this? The only examples I found where single key array indexes.

Regards,

Eric

2

At present you can only create single key array indexes. other key will be applied post indexscan.

If your second key all the time constant you can use WHEN clause.
CREATE INDEX ix1 ON db( DISTINCT ARRAY v.a FOR v IN OBJECT_VALUES(obj) WHEN v.c = “test” END);

1 Like
3

Sadly, “test” isn’t a constant and this query needs to be relatively performant. Would it help to create 2 indexes?

4

You can create two array indexes but that may not perform due to IntersectScan.

If your filters are equality only or at least one equality (make this first element of array) you can use following technique.

CREATE INDEX ix1 ON db( DISTINCT ARRAY [v.c, v.a] FOR v IN OBJECT_VALUES(obj) END);
SELECT * FROM db WHERE ANY v IN  OBJECT_VALUES(obj) SATISFIES  [v.c, v.a]  = ["test", 4] END
1 Like
5

Thanks a lot! I’m indeed checking the first element for equality. How would I go about checking if the 2nd element is larger than x using your query?

6

SELECT * FROM db WHERE ANY v IN OBJECT_VALUES(obj) SATISFIES [v.c, v.a] > ["test", 4] AND [v.c, v.a] < [SUCCESSOR("test")] END

These are too complex and care must be taken other wise you get unexpected results.

1 Like