How can i combine 2 queries in N1QL

SQL++
1

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
FROM Contacts
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'),
    1)

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

2 
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;
3

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

  {
    "code": 3000,
    "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;"
  }
4

Updated try again or add ON 1 =1

5

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 (
    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;
6

I did not realize alias already used

FROM Contacts AS c JOIN tracks AS t ON 1=1

7

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

8

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
9

thanks for explanation