Is it possible to combine two arrays into a single index

SQL++
, ,
1

Hi

I have a document which has 2 arrays, is it possible to create a single index whose participating key in index would be a merge of 2 arrays and not cartesian product.

Regards,
Venkat

2

Yes, only in Couchbase :slight_smile:
You can create a single FTS index with two arrays and avoid the cartesian product.
See: https://dzone.com/articles/arrays-in-json-modeling-querying-and-indexing-perf

3

In GSI if index field is same from both arrays ARRAY_CONCAT()

CREATE INDEX ix1 ON default (DISTINCT ARRAY v.id FOR v IN ARRAY_CONCAT(a,b) END);
SELECT * FROM default WHERE ANY v IN ARRAY_CONCAT(a,b)  SATISFIES v.id > 10 END;
2 Likes
4

Hi @vsr1

Thanks for the solution.

Can you also suggest how to get it work if one of the Array is Missing?

Regards,
Venkat

5

ARRAY_CONCAT(IFMISSINGORNULL(a,),IFMISSINGORNULL(b,))