I am trying to figure out if i can solve this issue directly in N1QL or if i need to use an SDK like NodeJs to get the data in a format I want.
I have docs which are of type farm and they belong to a tract. And a tract is part of a tract_info which means a tract_info doc has one or more tract in it. So what i like to do is get a count of all farm docs which are part of a tract_info.
So if i provide the individual key for my tract_info i can get the count via
SELECT COUNT(*) AS property_count
WHERE _type= 'farm'
AND Record.tract IN ARRAY_FLATTEN((
SELECT RAW d.tract_id
FROM Contacts d USE KEYS 'tract_info::0A840023-E5FB-442A-8D3E-ED93C597A8EC'),
to get the list of all Keys i would use
select meta().id, name from Contacts where _type = 'tract_info'
So the question is can i implement this into a single query and if possible pass the name from the 2nd query into the 1st one. So ideally i would like to have count and name per tract info
WITH tracks AS (SELECT ti.name, ti.tract_id FROM Contacts AS ti WHERE ti._type = 'tract_info')
SELECT COUNT(*) AS property_count, t.name
FROM Contacts AS c UNNEST tracks AS t
WHERE c._type = "farm" AND c.Record.tract IN t.tract_id
GROUP BY t.name;
So basically we do first query to get all track_info and then at the end join it which allows us to keep name and get count based on group by but it doesn’t like something with the where clause
"msg": "syntax error - at WHERE",
"query": "WITH tracks AS (\n SELECT ti.name,\n ti.tract_id\n FROM Contacts AS ti\n WHERE ti._type = 'tract_info')\nSELECT COUNT(*) AS property_count,\n t.name\nFROM Contacts AS t\n JOIN tracks AS t\nWHERE _type = \"farm\"\n AND Record.tract IN t.tract_id\nGROUP BY t.name;"
Updated try again or add ON 1 =1
Where would i add the ON 1=1 ? Also what does this do ?
Also your latest still does not work as you use
FROM Contacts AS t UNNEST tracks AS t
and it doesn’t like the 2 t aliases. The working one looks like this…
WITH tracks AS (
FROM Contacts AS ti
WHERE ti._type = 'tract_info')
SELECT COUNT(*) AS property_count,
FROM Contacts AS c
UNNEST tracks AS t
WHERE c._type = "farm"
AND c.Record.tract IN t.tract_id
GROUP BY t.name;
I did not realize alias already used
FROM Contacts AS c JOIN tracks AS t ON 1=1
No worries, its hard to blind code… I just wanted to make sure if someone comes across post and wants to use it does not fail because of that.
But can you explain more on the ON 1 =1 where I would put it and what it actually does
ANSI JOIN’s requires ON clause. As you don’t have OUTER JOIN. Alternative here are
- FROM Contacts AS c JOIN tracks AS t ON c.Record.tract IN t.tract_id
- Leave c.Record.tract IN t.tract_id in where clause and trick (hope it works) FROM Contacts AS c JOIN tracks AS t ON 1=1 i.e. ON clause always true and treat like Cartesian join
- Use UNNEST as you used