how to write a nested couchbase query which returns count and list of objects in spring data couchbase reactive?

Question

I have below query written in my spring data reactive couchbase repository class using @Query annotation

WITH subquery1 AS (
  SELECT COUNT(*) AS total_count
  FROM bucketName
  WHERE condition1
),
subquery2 AS (
  SELECT name, address, email
  FROM bucketName
  WHERE condition2
  OFFSET $offset LIMIT $limit
)
SELECT s1.total_count AS totalCount, s2.*
FROM subquery1 AS s1, subquery2 AS s2;

My Pojo Expected is like below

    // Expected Couchbase QueryResult Pojo

    class QueryResult {
       private Long totalCount;
       private List<Person> persons;
    }

    //// Person pojo

    class Person {
      private String name;
      private String address;
      private String email;
    }

My query is basically returning List of Persons with each items in list having totalCount.

I want to segregate totalCount and List, please help me with corrections.

Answer 1

I am not expert in spring data, but try this

WITH totalCount AS ( (SELECT RAW COUNT(1)
                      FROM bucketName
                      WHERE condition1)[0]
                   ),
     persons AS ( SELECT name, address, email
                  FROM bucketName
                  WHERE condition2
                  OFFSET $offset
                  LIMIT $limit
                )
SELECT totalCount, persons;

Also checkout https://www.couchbase.com/blog/offset-keyset-pagination-n1ql-query-couchbase/

If you need totalCount in all the elements of ARRAY

WITH totalCount AS ( (SELECT RAW COUNT(1)
                          FROM bucketName
                          WHERE condition1)[0]
                       )
 SELECT name, address, email, totalCount
 FROM bucketName
 WHERE condition2
 OFFSET $offset
 LIMIT $limit;

Answer 2

@axnet - have you looked into using the Pageable feature?

Page<Airport> findAllByIataNot(String iata, Pageable pageable);


Pageable pageable = PageRequest.of(0, 2).withSort(Sort.by("iata"));
Page<Airport> aPage = airportRepository.findAllByIataNot("JFK", pageable);