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

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

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" : [{...},{...}]
}

Solution

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)}