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