How can I nest purchases from Products without JOIN?

SQL++
1

Hello,

In the n1ql tutorial there are two tables like product and purchases, I want select the products and their purchases without join but not working:

SELECT

p.name

FROM product p

LEFT NEST purchases ON (META(p).id IN purchases.lineItems.product)


LIMIT 5;

Error:

No index available for ANSI nest term purchases

So how you do it ?

remove the code and add the code from the above their: https://query-tutorial.couchbase.com/tutorial/#40

2

You can’t. You are joining two keyspaces on a condition, you’re simply collating all joined documents into a field when using NEST. An index is required (in the version used) to support the join.

The only exception to requiring an index for this is if the right target’s key(s) could be identified by a field in the left target - if you were selecting purchases and nesting product here you could use the ON KEYS clause to bypass the need for an index.

3

This explains everything ANSI JOIN Support in N1QL - The Couchbase Blog

4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.