Composite index from an array of objects

SQL++
,
2

DISTINCT ARRAY [f.ac, f.ym, f.d] FOR f IN fd END

You are combining 3 fields into array and indexing as scalar array consist of these fields.
To qualify array index your predicate SATISFIES clause must have reference [f.ac, f.ym, f.d]

f.ac = “AF”

SELECT RAW meta().id 
FROM bucket 
WHERE ANY f IN fd SATISFIES [f.ac, f.ym, f.d] >= ["AF"] 
                        AND [f.ac, f.ym, f.d] < [SUCCESSOR("AF")] END;

f.ac = “AF” AND f.ym = 1911

SELECT RAW meta().id 
FROM bucket 
WHERE ANY f IN fd SATISFIES [f.ac, f.ym, f.d] >= ["AF",1911] 
                        AND [f.ac, f.ym, f.d] < ["AF", SUCCESSOR(1911)] END;

f.ac BETWEEN “AF” AND “BC” AND f.ym > 1900 AND f.ym < = 2000

SELECT RAW meta().id 
FROM bucket 
WHERE ANY f IN fd SATISFIES [f.ac, f.ym, f.d] >= ["AF",1900] 
                        AND [f.ac, f.ym, f.d] < ["BC", SUCCESSOR(2000)] 
              AND  f.ac BETWEEN "AF" AND "BC"  AND f.ym  > 1900 AND f.ym < = 2000 END;

One of the predicate ([f.ac, f.ym, f.d]) used to index selection and push the values index spans these must be superset (otherwise false negatives possible, i.e. indexer eliminate qualified values, result in wrong results). Other predicates (f.ac, f.ym) to eliminate false positives (i.e. eliminate extra values don’t give non qualified values)

These are complex and extra caution needed. Checkout

1 Like