CODEWITHSUNDEEP
couchbasesql++
Qiang Yu
Qiang Yu

Reputation: 27

How to pass a variable into Subquery with n1ql?

In my situation, entreprise(company) can have several sites(filiales), I want to get all the filiales with format array.

In the json entreprise(company), there is no information of sites(filiales), In the json sites(filiales), it has entreprise(company) uid. Json entreprise(company):

{
  "type": "entreprise",
  "dateUpdate": 1481716305279,
  "owner": {
    "type": "user",
    "uid": "PNnqarPqSdaxmEJ4DoMv-A"
  }
}

Json sites(filiales):

  {
  "type": "site",
  "entreprise": {
    "uid": "3c0CstzsTjqPdycL5yYzJQ",
    "type": "entreprise"
  },
  "nom": "test"
}

The query I tried:

  SELECT 
          META(entreprise).id as uid, 
          ARRAY s FOR s IN (SELECT d.* FROM default d  WHERE d.type = "site" AND  d.entreprise.uid = uid) END as sites, 
          entreprise.* 
        FROM default entreprise 
        WHERE entreprise.type = "entreprise";

Result: error

 {
    "code": 5010,
    "msg": "Error evaluating projection. - cause: FROM in correlated subquery must have USE KEYS clause: FROM default."
  }

Then i use alias:

SELECT 
  META(entreprise).id as uid, 
  ARRAY s FOR s IN (SELECT d.* FROM default d  WHERE d.type = "site" AND  d.entreprise.uid = META(entreprise).id) END as sites, 
  entreprise.* 
FROM default entreprise 
WHERE entreprise.type = "entreprise";

Result: sites array is empty.

Upvotes: 1

Views: 1353

Answers (2)

Jym
Jym

Reputation: 106

First you have to create an index on your site documents :

CREATE INDEX site_ent_idx ON default(entreprise.uid) WHERE type="site";

Then change your query to use the new index :

SELECT 
META(entreprise).id as uid, 
ARRAY s FOR s IN (
    SELECT site.* 
    FROM default as ent USE KEYS META(entreprise).id 
    JOIN default as site ON KEY site.entreprise.uid FOR ent
) END as sites, 
entreprise.* 
FROM default entreprise 
WHERE entreprise.type = "entreprise"

This solution should meet your needs.

Upvotes: 3

geraldss
geraldss

Reputation: 2445

You need to perform an index join from sites to enterprises. See https://dzone.com/articles/join-faster-with-couchbase-index-joins

After that, use GROUP BY and ARRAY_AGG() to collect the sites into arrays.

Upvotes: 0

Related Questions