How to USE KEYS in N1QL subquery

I want to run, basically, this, to show brewery info for any that make a Belgian-style Pale Ale:

SELECT name, city, state, country
FROM `beer-sample` brewery
WHERE type = 'brewery'
    AND brewery.brewery_id IN (
        SELECT beer.brewery_id
        FROM `beer-sample` beer
        WHERE style = "Belgian-Style Pale Ale"
    );

But of course I get “FROM in subquery must use KEYS clause:”. I cannot figure out the right USE KEYS clause. My best guess from what I’ve seen here is:

SELECT name, city, state, country
FROM `beer-sample` brewery
WHERE type = 'brewery'
    AND brewery.brewery_id IN (
        SELECT RAW beer.brewery_id
        FROM `beer-sample` beer
        USE KEYS brewery.brewery_id
        WHERE style = "Belgian-Style Pale Ale"
    );

But that yields zero rows, as does every other permutation I try. What am I missing?

Thanks in advance,

AFC

P.S. I know there are other ways of doing this; I am trying to specifically demonstrate IN subquery conditions

Hello,

We have added support for subqueries that do not require a USE KEYS clause. This will be released after the GA release. If you need this feature before then, please let us know.

Thanks,
Gerald

Thanks very much.

AFC

I want to execute sub-query,for e.g.

SELECT a.user_id
FROM default a
WHERE doc_type = 'login’
AND EXISTS(
SELECT b._id
FROM default b
USE KEYS a.user_id
WHERE doc_type=‘owner’
);

It contain two different document using same ‘name’ and using one ‘name’ i want to access another…but unfortunately i am not getting any values…What am I missing?

The value of USE KEYS should be the actual key-value key of the b document. Is that the case here?

Basically my first document is like…
{
“user_name”: “ank”,
“password”: “123”,
“doc_type”: “login”,
“user_id”: “vid”
}

And my 2nd document is like…
{
"_id": “vid”,
“address”: “”,
“DOB”: “”,
“alternate_contact”: “”
}

In this i am trying to access the 2nd document …using ‘user_id’ of a first document.

One document must contain the primary key (external key) of the second document…

1 Like

You said :

We have added support for subqueries that do not require a USE KEYS clause. This will be released after the GA release.

What is the “GA” ? And is this feature has been released?

I would like to query every documents that has the same FK. For example :

SELECT COUNT(*) as count FROM BUCKET bucket
WHERE `conditions ...`
       AND ANY item IN (SELECT subBucket.`fk` FROM BUCKET subBucket) SATISFIES item.`fk` = bucket.`fk` END	

This is a simplified query but this is what I would like to do and with the USE KEYS mandatory in subQueries I can’t achieve it.

The GA release is that 4.5 GA that supports what I mentioned.

Thank you for your quick answer !

Before getting on 4.5, could you tell me if their is some way to achieve this kind of behavior in 4.1? Even with some performance issues or with very complex queries? Because we’re still in huge development and not yet ready to engage before project close to release date.

Still sorry for the inconvenience.

In 4.1, you have to use two queries, unfortunately. Issue the subquery first, and then embed the results in the outer query. You will be better off upgrading if possible.

hii…
I am using one query like below. But giving below error.
Query:
SELECT meta(d).id,d.*,FIRST v FOR v IN ud WHEN d.updateDescriptionId = v.id END AS updateDescriptionDetails
FROM ChecklistLibrary AS d
USE KEYS [meta(d).id]
LET ud = ARRAY_FLATTEN((SELECT RAW d2.updateDescriptions FROM ChecklistLibrary d2
USE KEYS [“For_Profit:updateDescription”]),2)

ERROOR:
{
“code”: 3000,
“msg”: “Ambiguous reference to field d.”,

Please help…

Check the post N1QL subquery in select

Thanks a lot for your help. Really appreciate.