How could I add a count key to a N1QL query result

SQL++
,
1

I have a query:

SELECT p.ID, p.Name FROM Preferences p WHERE type="myType"

The result is a list of objects[{"ID": "123", "Name": "John"} ...]

I want to get a result JSON such as:

{"count": 5, "result": [{"ID": "123", "Name": "John"} ...]}

How could I do this using N1QL?

2

Solved!

SELECT
COUNT(t.ID) AS count,
ARRAY_AGG(t) AS results
FROM
(
  SELECT
    p.`ID`, p.`Name`
  FROM
   `Preferences` p 
  WHERE `type` = "myType"
) AS t
3

The approach works. If subquery generate lot of records, array_agg() needs to accumulate them.

Are you consider the following approaches.

  1. At the end of the SELECT query metrics has resultCount can you utilize that? (If Pagination queries may terminate early and may not reflect actual values.)
  2. Two different queries (count query, actual results)
  3. The following approach and check which performs better
    SELECT ARRAY_COUNT(res) AS count, res AS results
    LET res = (SELECT p.ID, p.Name FROM Preferences p WHERE type = “myType”);
1 Like