Index composite array fields

In case of array of objects user can only index one field or convert them into array or object and index them.

Index single array field: predicate is on multiple array fields the options are limited and queries perform poorly.

Create multiple indexes and query does Intersect Scans. No covering indexes. Number of indexes increases, Indexer need more memory and due to intersect scan queries can perform poorly and less throughput. Use single index and filter post index scan.  All predicates not pushed, disables other pushdowns and higher KV fetch rate and impacts performance and throughput.

Convert into Array or object:  You can index as array of fields or object. This loses flexibility and may not able to apply predicate individually due to that loses functionality.

Array or object comparison is whole. Mostly can use equality. Not able to do any other comparisons

In array index key if more than one field is present flatten them and index as if reset of the index keys are moved to right by that many positions. Queries will be sargable accordingly and generate spans.

FLATTEN/UNNEST/UNWIND DISTINCT ARRAY f(x) FOR x IN expr1 END;

f(x) must be Array Construct (i.e [v1.fname,v1.lname]). ARRAY expression is used many places and f(x) must be single expression. We take as array construct and do flatten based on modifier after DISTINCT. As far as query is concerned no syntax change but it needs to be properly sargable and spans needs to be generated. It should support nested syntax and only FLATTEN, Array Construct allowed on innermost one.

Example 1:  

CREATE INDEX ix1 ON default(c0, c1,  DISTINCT ARRAY FLATTEN_KEYS(v1.fname DESC, v1.lname ASC) FOR v1 IN a1 END,  c2,c3);

  ====> (c0,c1,v1.fname DESC ,v1.lname ASC ,c2,c3);

SELECT META().id

FROM default

WHERE c0 > 0 AND

c1 >= "a"  AND

ANY v1 IN a1 SATISFIES v1.fname = "mark"  AND v1.lname > "O" END;

Spans :

                           "spans": [

                               {

                                   "exact": true,

                                   "range": [

                                       {

                                           "inclusion": 0,

                                           "low": "0"

                                       },

                                       {

                                           "inclusion": 1,

                                           "low": "\"a\""

                                       },

                                       {

        "high": "\"mark\"",

                                           "inclusion": 3,

                                           "low": "\"mark\""

                                       },

                                       {

        "inclusion": 0,

                                           "low": "\"O\""

                                       }

                                   ]

                               }

                           ]