Getting required documents using N1QL query

SQL++
1

Hi,
My current goal is to get the documents from couchbase using N1QL query.
Problem: The current issue is, I have to get first set of 5 documents and then i have to write a query in such a way to get next set of 5 documents and that will keep going on until i reach my specific goal. In some very rarest cases i have a field which is not unique.

First 5 Records:

{
“empID” : 111,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “GA”,
“CreatedTime”: “2020-01-05T06:50:00.2483002Z”
},
{
“empID” : 222,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “GA”,
“CreatedTime”: “2020-01-05T07:45:00.2483432Z”
},
{
“empID” : 871,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “GA”,
“CreatedTime”: “2020-01-05T11:23:54.3402177Z”
},
{
“empID” : 345,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “AX”,
“CreatedTime”: “2020-01-05T15:02:43.1983221Z”
},
{
“empID” : 401,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “KL”,
“CreatedTime”: “2020-01-05T15:02:43.1983221Z”
},

Next 5 Records:
{
“empID” : 4300,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “LE”,
“CreatedTime”: “2020-01-05T15:02:43.1983221Z”
},
{
“empID” : 3201,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “LE”,
“CreatedTime”: “2020-01-05T16:45:00.6493431Z”
},
{
“empID” : 7601,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “LE”,
“CreatedTime”: “2020-01-05T17:23:54.3402177Z”
},
{
“empID” : 2001,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “AX”,
“CreatedTime”: “2020-01-05T18:34:20.7631011Z”
},
{
“empID” : 4001,
“empType”: “Contractor”,
“DateOfWork”: “2021-01-05”,
“GroupId”: “KL”,
“CreatedTime”: “2020-01-05T22:02:43.1983221Z”
}

im trying to fetch my first 5 records using the query:

SELECT * from TestBucket WHERE docType = ‘TEST-DOC’ AND DATE_FORMAT_STR(DateOfWork, ‘1111-11-11’)
= ‘2021-01-05’ order by createdTime limit 5.

and for the next set of records:

SELECT * FROM `TestBucket` WHERE docType = 'TEST-DOC' AND
DATE_FORMAT_STR(DateOfWork, '1111-11-11') = '2021-01-05'
and CreatedTime >= '2020-01-05T15:02:43.1983221Z' and meta().id != '401+KL'
ORDER BY CreatedTime, meta().id LIMIT 5

Note: id is here combination of employeeId and GroupId

If you observe, the 4th, 5th and 6th record have the same createdTime here which is very rare but since our records are almost 10000 a day there is chance of occurance. so when i fetch using the second query i am getting the 4th record in the next set of records. what can be the solution for fetching first few records and next set of records should not contain any duplications. i am not sure which combination to use to query the next set

2

The following blog has all the details . https://blog.couchbase.com/offset-keyset-pagination-n1ql-query-couchbase/

CREATE INDEX ix1 ON TestBucket (DATE_FORMAT_STR(DateOfWork, '1111-11-11'), CreatedTime, META().id) WHERE docType = 'TEST-DOC';

SELECT t.*, META(t).id  FROM `TestBucket` AS t WHERE t.docType = 'TEST-DOC' AND
DATE_FORMAT_STR(t.DateOfWork, '1111-11-11') = '2021-01-05'
and t.CreatedTime >= $starttime AND MET(t).id > $startkey
ORDER BY t.CreatedTime,  meta(t).id  LIMIT 5;

Start with $starttime “” , $startkey “”
Replace last document values for next query. AS META().id is Unique you will not have duplicates.

3

Hi, thank you for prompt response, but here my startKey is a string. how will couchbase compare met(t).id > $startKey

lets say my fourth record is ‘100+KL’
my fifth record is ‘50+KL’
my sixth record is ‘75+EA’
and have the same createdTime. if im doing meta().id > ‘100+KL’. i think i dont see the next two records in my response

4

you need to have sorting criteria, You can’t have random order.
ORDER BY t.CreatedTime, meta(t).id
It sorts by CreatedTime, when there is duplicates in CreatedTime it does sort on META().id within those duplicates.

If you start with “” for given date starts from lowest createdTime.

Also t.CreatedTime >= $starttime is grater than equal i.e. means it will handle partial results of same createTime