Uploaded image for project: 'Couchbase Server'
  1. Couchbase Server
  2. MB-32506

Index composite array fields



    • Improvement
    • Resolution: Fixed
    • Critical
    • 7.1.0
    • 6.5.0
    • query



      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.
      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\""

      ID Requirement Comments
        Introduce new modifier for Array Index key. (
      or UNWIND or UNNEST). Mark it is reserved keyword.
        Indexer needs to detect this modifier and flatten the array and shift the index keys while indexing  
        The positions of index keys will be shifted  accordingly (in the spans/group/aggregates)  
        Sargable the index based on the individual flatten keys  
        Generate spans based on individual flatten keys  
        Support for both DISTINCT ALL, ALL modifiers  
        UnnestScan should support new index keys.  

      // code placeholder

        UNNEST and non-unnest IndexScan combine and do index Selection to avoid IntersectScan  


        Issue Links

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.



              ajay.bhullar Ajay Bhullar
              Sitaram.Vemulapalli Sitaram Vemulapalli
              0 Vote for this issue
              5 Start watching this issue