How to create N1Ql Query were subquery gets Count based on criteria of main query

SQL++
1

I am trying to combine 2 Queries where i get a list of all my tracts as well as pull from my farm all emails which are not bounced etc and return as one.

the first query i have is straight forward and simple

SELECT name, tract_id from Contacts where _type = 'tract_info'
and my return looks like this

      [ {
        "name": "Mariners Bluff",
        "tract_id": [ 12339,  12335, 12335, 12336, 12337, 12338 ]
      },
      {
        "name": "Cabo del Mar",
        "tract_id": [  13260, 13261 ]
      }]

here is a sample query to get a list of all emails which i don’t need just the total count

SELECT COUNT(e.address)
FROM Contacts c
UNNEST c.emails e
WHERE c._type= 'farm'
    AND ARRAY_COUNT(c.emails) > 0
    AND e.bounce IS MISSING
    AND (e.dnmm IS MISSING
        OR e.dnmm = FALSE)
    AND c.tract IN [ 12339, 12335, 12335, 12336, 12337, 12338 ]

So i tried it via subquery below with no luck , So how can i achieve this goal ?

SELECT d.name,
       (
           SELECT COUNT(e.address)
           FROM Contacts c
           UNNEST c.emails e
           WHERE c._type= 'farm'
               AND ARRAY_COUNT(c.emails) > 0
               AND e.bounce IS MISSING
               AND (e.dnmm IS MISSING
                   OR e.dnmm = FALSE)
               AND c.tract IN d.tract_id ) AS EmailCount
FROM Contacts d
WHERE d._type = 'tract_info'`
2

That is correlated subquery which may need 7.0.0

SELECT d.name,
  SUM(ARRAY_COUNT(ARRAY 1
                  FOR e IN c.emails
                  WHEN e.address IS NOT NULL AND e.bounce IS MISSING AND (e.dnmm IS MISSING OR e.dnmm = FALSE)
                  END)) AS cnt
FROM Contacts d
LEFT UNNEST d.tract_id AS tid
LEFT JOIN Contacts AS c
ON tid = c.tract AND c._type = 'farm'
WHERE d._type = 'tract_info' AND d.tract_id IS NOT NULL
GROUP BY d.name;

CREATE INDEX ix1 ON Contacts(tract_id, name) WHERE _type = 'tract_info'

CREATE INDEX ix2 ON Contacts(tract, ARRAY_COUNT(ARRAY 1
                  FOR e IN c.emails
                  WHEN e.address IS NOT NULL AND e.bounce IS MISSING AND (e.dnmm IS MISSING OR e.dnmm = FALSE)
                  END)) WHERE _type = 'farm';

See if query covers. If not remove second key from ix2