Covering Indexes in JOIN query using UNNEST

Hi ,
I’m trying to build a covering index for the following query:

SELECT var.valueB FROM test AS a JOIN test AS b ON a.subId = b.subId
UNNEST a.valueA var
WHERE a.type = “TYPEA”
AND b.type = “TYPEB”
AND var.valueB = “testValueA”
LIMIT 1

I created the following indexes:

CREATE PRIMARY INDEX PRIMARY_INDEX ON test

CREATE INDEX TEST_INDEX_SUB_ID ON test (subId)

CREATE INDEX TEST_INDEX_VALUEB_WITH_SUB_ID ON test (subId, ALL (ARRAY var.valueB FOR var IN valueA END)) WHERE type = “TYPEA”

but this will not use the index to cover the query. Any suggestions?
I had success with the same query without the JOIN (creating an index without ‘subId’), but it’s part of the requirement.
Is this possible in the current Couchbase (v6)?
Thank you for your time.
Regards,

iago

For UNNEST to use ARRAY index. First index key must be ALL, Unnest Alias and Index key variable must be same.
https://docs.couchbase.com/server/6.0/n1ql/n1ql-language-reference/indexing-arrays.html

CREATE INDEX  `ix1`  ON  `test`  ( ALL ARRAY ua.valueB FOR ua IN valueA END, `subId` ) WHERE  type = "TYPEA";
CREATE INDEX  `ix2`  ON  `test`  (  `subId` ) WHERE  type = "TYPEB";
SELECT ua.valueB 
FROM test AS a 
JOIN test AS b ON a.subId = b.subId
UNNEST a.valueA ua
WHERE a.type = “TYPEA”
AND b.type = “TYPEB”
AND ua.valueB = “testValueA”
LIMIT 1
1 Like

Thank you for your quick reply! That indeed solve the issue! I thought I tried all combinations, but it seems I’ve missed having ALL as the first Index key.

Anyway, this brings me to the next issue. The data we want to index is actually is stored in arrays of arrays, and have identifiers (‘cId’) for the container arrays. We are trying to create the index only for the ones with cId = “c1”:

CREATE INDEX `TEST_INDEX_VALUEB_WITH_SUB_ID` ON `test` (
  ALL (ARRAY (
    ALL ( ARRAY var2.`valueC` FOR var2 IN var1.`valueB` 
          END)) 
    FOR `var1` IN `valueA` 
    WHEN `var1`.`cId` = "c1" END), `subId`)
WHERE `type` = "TYPEA"

When we perform the query:

SELECT var2.valueC FROM test AS a JOIN test AS b ON a.subId = b.subId
UNNEST a.valueA var1
UNNEST var1.valueB var2
WHERE a.type = "TYPEA"
AND b.type = "TYPEB"
AND var1.cId = "c1"
AND var2.valueC = "testValueA"
LIMIT 1

it’s using index (and covering), but it’s not giving any results even if there are. If I remove the index from couchbase and perform the query again, the results will show. Is this a bug in couchbase or am I doing something wrong?
Again, thank you for your help.

Here is the example data I’m using:

INSERT INTO `test` ( KEY, VALUE ) 
VALUES(
"id1A",
{
  "type":"TYPEA",
  "subId":"id1",
  "valueA": [
   {
      "cId" : "c1",
      "valueB": [
        {
            "valueC": "testValueA"
        }
      ]
   },
   {
      "cId" : "c2",
      "valueB": [
        {
            "valueC": "testValueB"
        }
      ]
   }
  ]
}
)

INSERT INTO `test` ( KEY, VALUE ) 
VALUES(
"id1B",
{
  "type": "TYPEB",
  "subId": "id1",
  "valueC": "testValueB"
}
)

Opened MB-33485

Workaround is change ALL to DISTINCT and explicitly include ARRAY as index key. This may increase index size.

CREATE INDEX `TEST_INDEX_VALUEB_WITH_SUB_ID` ON `test` (
  DISTINCT (ARRAY (
    DISTINCT ( ARRAY var2.`valueC` FOR var2 IN var1.`valueB` 
          END)) 
    FOR `var1` IN `valueA` 
    WHEN `var1`.`cId` = "c1" END), `subId`, `valueA` )
WHERE `type` = "TYPEA"

Thank you again for the help. This solution would probably make our indexes too big (the data might be deeper than two nested arrays). I’ll keep an eye on that ticket.