Total Result Count

SQL++
,
1

Suppose i have a query
SELECT t2.s.seller_id, t2.s.seller_name, ARRAY_AGG({t2.Payments, t2.a.account_id, t2.a.account_name}) AS Accounts
FROM ( SELECT t1.s, t1.a, ARRAY_AGG({p.id,p.account_id,p.amount}} AS Payments
FROM (SELECT s, a, p
FROM default AS s
JOIN default AS a ON s.seller_id = a.seller_id
JOIN default AS p ON a.account_id = p.account_id
WHERE s.type = “Seller” AND a.type = “Account”
AND p.type = “Payment” AND s.seller_id IS NOT NULL) AS t1
GROUP BY t1.s, t1.a) AS t2
GROUP BY t2.s ORDER BY t2.s.seller_id DESC LIMIT 10 OFFSET 0;

Its give me the list of result Inside array.For pagination purpose i have used Limit and offset. That is why i need to count the whole result .Is there any way so that we can get the total count of result .Other wise i need to run a separate Count(*) query to get the total result count.

2

As above query must require sort and will not use index sort.

See if you can use sortCount from metrics of the query results. As query required sort it must produce all possible values and those will be input to sort. So sortCount will reflect that value (If not ORDER BY or query uses index order, sortCount will not present, this particular query it will present)

  "metrics": {
        "elapsedTime": "3.193804ms",
        "executionTime": "3.130265ms",
        "resultCount": 12,
        "resultSize": 2485,
        "sortCount": 12
    }

If that doesn’t work checkout the following post

Also the following might be useful because join, group, pagination expensive

OFFSET and Keyset Pagination in N1QL Query | Couchbase

3

“As above query must require sort and will not use index sort.” what do you mean by this line…can you please elaborate this…In my query i have used ORDER BY clause. Another question is how can i found the

"metrics": {
        "elapsedTime": "3.193804ms",
        "executionTime": "3.130265ms",
        "resultCount": 12,
        "resultSize": 2485,
        "sortCount": 12
    }

in my query result? I did not get this in my couchbase query workbench and also when i call this query from my node js application.

4

cbq shell , REST API gives. Query work bench doesn’t display

https://docs.couchbase.com/nodejs-sdk/2.6/n1ql-queries-with-sdk.html#streaming-rows
process meta you should have it