Use result of CTE in next CTE

N1QL
1

In my database I want to chain multiple common table expressions. Here is an example to illustrate what I want to do:
In my database there are the following documents:

{
“id”: “12345”,
“name”: “Small Arabica Cup”,
“price”: 10,
“beanId”: “xyz”,
“type”: “CoffeeCup”
}
{
“id”: “xyz”,
“name”: “Arabica”,
“countryId”: “abc”,
“type”: “CoffeeBean”
}
{
“id”: “abc”,
“country”: “Brazil”,
“type”: “CoffeeOriginCountry”
}

I have written the following N1QL query where I want to use multiple CTE’s in order to avoid using join statements:

WITH originCountryCTE AS (
    SELECT country.*,
           META(country).id AS id
    FROM bucket country
    WHERE country.type ="CoffeeOriginCountry" ),
beanCTE AS (
    SELECT bean.*,
           META(bean).id AS id
    FROM bucket bean
    LET country = FIRST countryObj FOR countryObj IN originCountryCTE WHEN countryObj.id = bean.countryId END
    WHERE bean.type ="CoffeeBean" )
SELECT cup.*,
        bean
FROM bucket cup
LET bean = FIRST beanObj FOR beanObj IN beanCTE WHEN beanObj.id = cup.beanId END
WHERE cup.type="CoffeeCup"

But I keep on getting the following error:

“Unable to run subquery - cause: FROM in correlated subquery must have USE KEYS clause: FROM bucket.”

Is this even possible to do? Is there something wrong with my syntax?

Thanks for the help!

1 Like
2

AS originCountryCTE doesn’t need outside move inside

WITH beanCTE AS (
    WITH originCountryCTE AS ( SELECT country.*, META(country).id AS id
                               FROM bucket country
                               WHERE country.type ="CoffeeOriginCountry" )
    SELECT bean.*,
           META(bean).id AS id
    FROM bucket bean
    LET country = FIRST countryObj FOR countryObj IN originCountryCTE WHEN countryObj.id = bean.countryId END
    WHERE bean.type ="CoffeeBean" )
SELECT cup.*,
        bean
FROM bucket cup
LET bean = FIRST beanObj FOR beanObj IN beanCTE WHEN beanObj.id = cup.beanId END
WHERE cup.type="CoffeeCup"
3

Thank you very much @vsr1, that makes complete sense for this query.

But there are other use cases where I have multiple CTE’s and in some of those I need to reference another CTE. For example the following adjusted query:

WITH originCountryCTE AS (
    SELECT country.*,
           META(country).id AS id
    FROM bucket country
    WHERE country.type ="CoffeeOriginCountry" ),
countryClimateCTE AS (
    SELECT climate.*,
           META(climate).id AS id
    FROM bucket climate
    LET country = FIRST countryObj FOR countryObj IN originCountryCTE WHEN countryObj.id = climate.countryId END
    WHERE bean.type ="CoffeeBean" )
beanCTE AS (
    SELECT bean.*,
           META(bean).id AS id
    FROM bucket bean
    LET country = FIRST countryObj FOR countryObj IN originCountryCTE WHEN countryObj.id = bean.countryId END
    WHERE bean.type ="CoffeeBean" )
SELECT cup.*,
        bean
FROM bucket cup
LET bean = FIRST beanObj FOR beanObj IN beanCTE WHEN beanObj.id = cup.beanId END
WHERE cup.type="CoffeeCup"

Is something like this possible with N1QL?

4

Track via MB-30813

5

Great! Thank you very much @vsr !

Do you maybe know when the Cheshire Cat version is scheduled for release?