Couchbase N1QL want to return items and count

Question

Trying to figure out how to make my couchbase return an object like so:

{
  items: [],
  totalItemsCount: T<number>,
}

My select is formatted like so:

SELECT  a.*, (  SELECT COUNT(*) FROM table b WHERE b.environment = "test" AND b.DocType = "GM360.User") as Count 
FROM table a WHERE a.environment = "test" 
AND a.DocType = "Moderator.User" 
limit 5 offset (5 * (1 -1) )

And the result looks like:

[
  { Accounts: [], UserId: 1,  Count: 199 },
  { Accounts: [], UserId:, 2, Count: 199 },
]

Answer 1

The following query gives result object you are expecting. If that is not explain the problem more clearly.

SELECT (SELECT RAW a
        FROM table AS a
        WHERE a.environment = "test" AND a.DocType = "Moderator.User") AS items,
       (SELECT RAW COUNT(1)
        FROM table b
        WHERE b.environment = "test" AND b.DocType = "GM360.User")[0] AS totalItemsCount;

OR

SELECT SUM(CASE WHEN a.DocType = "GM360.User" THEN 1 ELSE 0 END) AS totalItemsCount,
       ARRAY_AGG(CASE WHEN a.DocType = "Moderator.User" THEN a ELSE MISSING END) AS items
FROM table AS a
WHERE a.environment = "test" AND a.DocType IN ["Moderator.User", "GM360.User"];