How to pass a variable into Subquery with n1ql?

SQL++
,
1

In my situation, entreprise(company) can have several sites(filiales), I want to get all the filiales with format array. I use version 4.5.0-2601 Community Edition.

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.

2

You need to use an index join. See https://dzone.com/articles/join-faster-with-couchbase-index-joins

If these are not available in 4.5.0, take a look at 4.5.1.

1 Like
3

Thank you! It works. :slight_smile:

1 Like