Index join on arrays

SQL++
1

Hi there,

I have two type of documents :

TYPE/key1

{
    "key" : ["key1", "key2"],
    "data" : "toto",
    "type": "product"
}

TYPE/key3

{
    "key" : ["key3", "key4"],
    "data" : "toto",
    "type": "product"
}

KEYS/key1

[
    "key1",
    "key3"
]

I need to do a subquery or a join request to get the two documents from the key1.

I have this index :

CREATE INDEX `key` ON default(distinct (array `k` for `k` in `key` end)),`key`)

Example :

SELECT * from default
WHERE ANY k IN `key`
    SATISFIES k IN (
        SELECT RAW transco from products transco
        USE KEYS ['KEYS/key1']
        )
    END

But It doesn’t work!

2

SELECT * from default
WHERE ANY k IN key
SATISFIES k IN ARRAY_FLATTEN((
SELECT RAW transco from products transco
USE KEYS [‘KEYS/key1’]
),2)
END

3

This doesn’t work, it does a full scan!

It is possible to use JOIN in this case? Maybe it is better than a subquery

4

subqueries are evaluated at execution and plan generated at prepare time. If the index key values are not constants it uses prepare time.
You can use CB 5.5 and use ANSI JOINS

5

I’m afraid that we can’t use CB 5.5 for now as we are in production and it is still in beta!
So is there any solution for the subquery then?

6

The Join is between 2 fields, unless you have relation with field and document key it is not possible

7

Ok, I understand that I can’t do a JOIN with the current version.

How can I get subquery to work?

Evaluating the two request separately works perfect, but not in the same request!

8

Subquery works but not efficient due to values are not constants and due to that not able to pass it indexer query requires do full indexscan and apply predicates

Req 1
SELECT RAW transco from products transco
USE KEYS [‘KEYS/key1’]

Req2
Use output from Req1 as named or query parameters with adhoc=true from SDKs

SELECT * from default
WHERE ANY k IN key
SATISFIES k IN $keys
END

9

Got it, will do.

Thanks a lot.

10

Hello,

I tired the LET clause, as the documentation say that :

If the LET variable is referenced in the WHERE clause, then it is evaluated before the WHERE clause

But it is still not working. Here is the query :

SELECT * from default
LET `keys` = ARRAY_FLATTEN((SELECT RAW transco from products transco USE KEYS [‘KEYS/key1’] ),2)
WHERE ANY k IN key
         SATISFIES k IN `keys`
      END

Can you explain why?

11

Can you more specific?
Query optimizer decides plan during prepare time and decides which one push to indexer. Only constants can pushed to indexer. In your case keys are not a constant. The needs to be evaluated at the runtime. So it needs to do full index scan.

check the EXPLAIN.
Also Check https://dzone.com/articles/understanding-index-scans-in-couchbase-50-n1ql-que