Index creation on nested structures makes the query fail

SQL++
, ,
1

Hi,
I’m finding a strange behaviour in Couchbase.
Assuming we have a bucket called ‘a_bucket’ with a primary index, and the following document with a covering index on the valueA:

INSERT INTO `a_bucket` ( KEY, VALUE )
VALUES("an_id",{
  "list1": [
    {
      "id": "a_sublist_id",
      "valueA": "value1",
      "list2": [
        {
          "valueB": "value2"
        }
      ]
    }
  ]
})

CREATE INDEX `valueA_index` ON `a_bucket`(
(ALL(ARRAY(`_var0`.`valueA`) FOR `_var0` 
IN ARRAY_FLATTEN(ARRAY `x` FOR `x` WITHIN SELF WHEN x.id = "a_sublist_id" END, 1) END)))
WHERE ANY `x` WITHIN SELF SATISFIES `x`.`id` = "a_sublist_id" END

If we perform the following query:

SELECT
`_a`.`valueA` as valueA, 
`_b`.`valueB` as valueB
FROM a_bucket as comp_a
UNNEST ARRAY_FLATTEN(ARRAY x FOR x WITHIN comp_a WHEN x.id = "a_sublist_id" END, 1) AS _a
UNNEST `_a`.`list2` AS `_b`
WHERE ANY x WITHIN comp_a SATISFIES x.id = "a_sublist_id" END
AND `_b`.`valueB` = "value2" 
AND `_a`.`valueA` = "value1"
LIMIT 1

We get the expected result:

[
  {
    "valueA": "value1",
    "valueB": "value2"
  }
]

The problem comes when I try to add another index for the nested value (valueB) as follows:

CREATE INDEX `valueB_index` ON `a_bucket`
((ALL (ARRAY(ALL(ARRAY(`_var1`.`valueB`) FOR `_var1` 
IN (`_var0`.`list2`) END)) FOR `_var0` 
IN ARRAY_FLATTEN(ARRAY `x` FOR `x` 
WITHIN SELF WHEN ((`x`.`id`) = "a_sublist_id") END, 1) END)))
WHERE ANY `x` WITHIN SELF SATISFIES `x`.`id` = "a_sublist_id" END

If we perform the same query, the result is:

{
  "results": []
}

Is this a bug or am I doing something wrong? Is there a way to avoid this behaviour having both indexes working.
If we remove the condition regarding ‘valueA’, the query behaves as expected (using the second index).
I don’t need to use both indexes when performing the query. I understand that it’s not possible to just use covering indexes for the query, so it’s not a problem to just use one and fetch the other value from the document.
This is a simplified example just to demonstrate the behaviour, but the document structure is part of the specifications (cannot be changed) and both indexes must exist to be able to query valueA and valueB independently.
Any help would be appreciated.

Cheers,

iago

2

Index must be simple and shouldn’t use so complex.

3

Thank you for the quick response. I was waiting for more answers, but since this is the only one after almost two weeks, I’ll have to accept it. We are still evaluating couchbase as an alternative for our product, but the covering indexes is a critical feature we need to have working.

If we simplify the structure to something like:

INSERT INTO `b` ( KEY, VALUE )
VALUES("an_id",{
      "obj1": {
        "param1": "value1"
      },
      "obj2": {
        "param2": "value2"
      },
       ...
      "objN": {
         "paramN": "valueN"
      }
})

If we are performing a select that looks like the following:

SELECT COUNT(ob1.param1)
FROM b
WHERE obj1.param1 = "value1" 
AND obj2.param2 = "value2"

I understand the simplest way to have a covering index would be to create an index of the whole object, but our objects can get really big (i.e. several hundred params) and not all parameters need to be indexed. If we create an index for param1 and param2, is there a way to create a third index that covers their relationship, so, when performing the select there is no need to fetch the full object?
Thank you for your time.

Regards,

iago

4

If need covering create composite index.

create index ix1 on bucket(obj1.param1, obj2.param1)

5

Thank you, this is exactly what I was looking for.