N1QL Query with SubQuery Issue

SQL++
1

As Prefix, i have docs which provide more detail about a farm which look like this

{
  "_id": "EDA9460D-26F7-4F63-A804-36FB0CB395CB",
  "_type": "tract_info",
  "name": "Crown Park",
  "city": "Laguna Niguel",
  "tract_id": [
    7340
  ]
}

my query has a few issues if i use

 select 
meta().id as DocId,
mailingAddress.address || " " || mailingAddress.city || " " || mailingAddress.state || " " || mailingAddress.zip ||"-" || mailingAddress.zip4 as mailing_address,
(select name from Contacts as d where _type ="tract_info"
and ANY b IN d.tract_id SATISFIES b = 7340 END) as tract_name, tract
from Contacts where _type="farm" and tract = "7340"

i get a result which is correct but i don’t like the format of

 {
    "DocId": "farm::01BE8B24-A7F6-441D-94A1-448B41E1DC6D",
    "mailing_address": "Dream Ave LagunaForest CA 92822-1423",
    "tract": "7340",
    "tract_name": [
      {
        "name": "Crown Park"
      }
    ]
  }

what i would like to get is

{
“DocId”: “farm::01BE8B24-A7F6-441D-94A1-448B41E1DC6D”,
“mailing_address”: " Dream Ave Laguna Forest CA 92822-1423",
“tract”: “7340”,
“tract_name”: “Crown Park”
}

second issue, if i try to use the tract number from the doc but i dont get any data back and i am a bit confused on how to use the “USE Key” in this case

select 
meta().id as DocId,
mailingAddress.address || " " || mailingAddress.city || " " || mailingAddress.state || " " || mailingAddress.zip ||"-" || mailingAddress.zip4 as mailing_address,
(select name from Contacts as d where _type ="tract_info"
and ANY b IN d.tract_id SATISFIES b = tract END) tract_name, tract
from Contacts where _type="farm" and tract = "7340"
2

Use ANSI JOIN Example 12 https://blog.couchbase.com/ansi-join-support-n1ql/

SELECT
META(f).id AS DocId, f.tract,
     f.mailingAddress.address || " " || f.mailingAddress.city || " " ||
     f.mailingAddress.state || " " || f.mailingAddress.zip ||"-" || f.mailingAddress.zip4 AS mailing_address,
     t.name AS tract_name
FROM Contacts AS f
JOIN Contacts AS t
ON t._type ="tract_info" AND ANY b IN t.tract_id SATISFIES b = f.tract END
WHERE f._type="farm" and f.tract = "7340"

CREATE INDEX ix1 ON Contacts(tract, mailingAddress) WHERE _type="farm";
CREATE INDEX ix2 ON Contacts(DISTINCT ARRAY v FOR v IN tract_id END, name) WHERE _type="tract_info";
3

Thanks that works, and gets me the format i want. But what am i missing that i cant to seem to pass the tract from main query row to the tract_id condition in subquery. That the query works i know, since when i set it to fixed value it returns the desired data.

4

That is correlated sub query. At present all correlated subqueries must have USE KEYS. In your case you don’t have document key in outer query. Only option is use ANSI JOIN

5

Is there any plan to allow correlated sub queries in a future release ? Sometimes you just don’t have the chance to use the Key as there can be multiple key values for a doc.