How to write SQL++ Couchbase query which returns total count and paged data?

Question

I have a below query

SELECT count_data.count, paged_data.* FROM
(
  (SELECT count(*) as count FROM main_data) AS count_data,
  (SELECT hosp_d.* FROM main_data AS hosp_d LIMIT 5) AS paged_data
)

My expected output json is like below

{
  "count" : 5,
  "paged_data" : [{...},{...}]
}

Answer 1

Here's another option, using a CTE. This approach might not be as performant, because ALL the results are in the CTE, and then LIMIT/OFFSET is applied in the SELECT. I'd definitely benchmark and measure to make sure the performance is okay, but this approach will get your results close to the form you want:

WITH articlesData AS (
  SELECT
     field1,
     field2,
     COUNT(*) OVER() AS totalCount
  FROM <collection>
  JOIN <whatever>
  WHERE <predicates>
)
SELECT VALUE { 
  "count": articlesData[0].articlesCount,
  "paged_list" : (SELECT x.* FROM articlesData x LIMIT 5 OFFSET 0)}

Answer 2

One approach that I have used is "pagination with total count", using the OVER window function.

Something like:

SELECT
   field1,
   field2,
   COUNT(*) OVER() AS totalCount
FROM <collection>
JOIN <whatever>
WHERE <predicates>
LIMIT 20
OFFSET 0

Answer 3

SELECT 
  (SELECT RAW count(1) FROM main_data)[0] AS count_data,
  (SELECT hosp_d.* FROM main_data AS hosp_d LIMIT 5) AS paged_data;

This is performance wise optimal.

SELECT RAW count(1) FROM main_data

Above query use collection stats for count because no WHERE clause and single count(1) expression

(SELECT hosp_d.* FROM main_data AS hosp_d LIMIT 5)

Above query only fetch 5 documents.