How to get the Total Doc Count in SubQuery without extra Query

I am wondering if there is a way to get not only the newest record but also the recordcount without an additional query. Using the sample

WITH data AS ( [{"_type":"farm","Record":{"apn":"65338104", "PropertyAddress": "1 Infinite Loop; Cupertino, CA 95014"}},
                {"_type":"farm","Record":{"apn":"65338108", "PropertyAddress": "2 Infinite Loop; Cupertino, CA 95014"}},
                {"_type":"listing", "ParcelNumber":"653-38-104", "Record":{"Dates":{"ModificationTimestamp":"2022-03-28T21:26:45.594-07:00"}}},
                {"_type":"listing", "ParcelNumber":"653-38-104", "Record":{"Dates":{"ModificationTimestamp":"2022-02-28T21:26:45.594-07:00"}}}
               ])

i can use the below query to get this info but I am not sure thats the most effective way as it seems to be an extra Querry.

SELECT f.Record.PropertyAddress AS farm,
       (
           SELECT p1.ParcelNumber, p1.Record
           FROM p AS p1
           ORDER BY p1.Record.Dates.ModificationTimestamp DESC
           LIMIT 1)[0] AS lisiting,
 (
           SELECT RAW count(*) 
           FROM p AS p1)[0] AS lisitingCount

FROM data AS f 
LEFT NEST data AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
                       AND p._type = "listing"
WHERE f._type = "farm"
LIMIT 2;

Also, how could i change the where clause to only return Docs that lets say have more then 1 listingCount

SELECT d.farm,
       (
           SELECT p1.ParcelNumber, p1.Record
           FROM d.p AS p1
           ORDER BY p1.Record.Dates.ModificationTimestamp DESC
           LIMIT 1
       )[0] AS lisiting,
       ARRAY_LENGTH(d.p) AS lisitingCount
FROM (SELECT f.Record.PropertyAddress AS farm, p
      FROM data AS f
      LEFT NEST data AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "") AND p._type = "listing"
      WHERE f._type = "farm") AS d
WHERE ARRAY_LENGTH(d.p) > 1
LIMIT 2;

Ok a question.

since this is another select query for the from clause do i actually save time using this vs running it like my query ? Both have 3 Query’s ?
But i guess i need it if i want to select only records that have more then ) or so listings

One i suggest is streaming query not actual fetch of queries.
As result of NEST values can’t be used in WHERE doing in parent query

As you need only listingCount > 1 that can be achieved only with approach i suggested

Thanks, as for the streaming, do you have any links for NodeJs as the only stuff i can find is for java when googling couchbase N1QL streaming

N1QL always streams results when the row(document ) ready. When you do results.getrow() (not sure exact api, i am not expert on client) Those gives when available.

Very high level

Example:
SELECT d.*
FROM default d
WHERE d.x = 10;
If you do EXPLAIN, you see various operators. Each operator works on different thread in parallel
In above case IndexScan, Fetch, Filter, InitialProjection, FinalProjection,Stream
each one works different document. Once operator done with document it sends to next operator.
As document reach Stream it sends to client.
For example Order, GROUP operators are blocking because those has to holds results to compute (i.e. all input must exast before it releases first document)

Ok i gave the sample you provided a try but didn’t get any data back. I then removed the where filter for Array > 0 which got me records galore. The interesting part is that i got records which had listingCount : 0
but no other data. In my case the rets bucket / listing has 5 million records and the Contacts / farm has 5k

Here is the query i used

SELECT d.farm,
       (
           SELECT p1.ParcelNumber,
                  p1.Record
           FROM d.p AS p1
           ORDER BY p1.Record.Dates.ModificationTimestamp DESC
           LIMIT 1 )[0] AS lisiting,
       ARRAY_LENGTH(d.p) AS lisitingCount
FROM (
    SELECT f.Record.PropertyAddress AS farm,
           p
    FROM Contacts AS f LEFT NEST rets AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
        AND p._type = "listing"
    WHERE f._type = "farm") AS d
WHERE ARRAY_LENGTH(d.p) > 1
LIMIT 2;

You are doing LEFT NEST and doing in parent ARRAY_LENGTH(d.p) > 1 (which eliminates null projected)
Just do NEST and see what you get. Without data not able to tell why no results. Query seems right.