Styjun

ESTA Travel not Authorized. Accepted twice before!

BIP-23 criticism: Is bitcoin PoW actually sha256+merkleGeneration? Or have I misunderstood coinbase/append?

What does a Nintendo Game Boy do when turned on without a game cartridge inserted?

Why is there an extra "t" in Lemmatization?

Why don't humans perceive sound waves as twice the frequency they are?

Why teach C using scanf without talking about command line arguments?

Longtable of size textwidth exceeds right margin

TCP connections hang during handshake

Does unblocking power bar outlets through short extension cords increase fire risk?

why neutral does not shock. how can a neutral be neutral in ac current?

Aren't all schwa sounds literally /ø/?

Can a creature sustain itself by eating its own severed body parts?

Improving an O(N^2) function (all entities iterating over all other entities)

Linux ext4 restore file and directory access rights after bad backup/restore

When we are talking about black hole evaporation - what exactly happens?

"This used to be my phone number"

Does the Bracer of Flying Daggers really let a thief make 4 attacks per round?

Which modern firearm should a time traveler bring to be easily reproducible for a historic civilization?

Is it ethical to tell my teaching assistant that I like them?

Redirection operator, standard input and command parameters

Why is this guy handcuffed censored?

What is really the difference between uniform velocity and constant velocity?

How was Luke's prosthetic hand in Episode V filmed?

Will copper pour help on my single-layer PCB?

How do I use paginate with 'cursors' in N1ql?

NoSQL Query Language, UnQL? N1QL? CouchBase, C embedded libraryObtaining last revisions in Couchbase with N1QLCouchbase N1QL query against existing viewdefault object in N1QL resultsSelect multiple counts for each day in date range using N1QLN1QL query on arrayHow to query documents which are arraysCouchbase Index and N1QL QueryUsing Couchbase String functions in N1QL responseOffset results of children nodes from N1QL Analytics query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

4

1

I need to paginate a Couchbase N1ql Query.

I am aware of pagination with OFFSET, but it is more efficient to designate the start and end point.

I see documentation about startkey_docid, but none about how to us this in N1ql.

How do I paginate with cursors, or something similar, in N1ql?

couchbase n1ql database-cursor

share|improve this question

edited Mar 26 at 12:34

Joshua Fox

asked Mar 26 at 10:18

Joshua FoxJoshua Fox

8,6501111 gold badges5252 silver badges8383 bronze badges

add a comment | 

4

1

I need to paginate a Couchbase N1ql Query.

I am aware of pagination with OFFSET, but it is more efficient to designate the start and end point.

I see documentation about startkey_docid, but none about how to us this in N1ql.

How do I paginate with cursors, or something similar, in N1ql?

couchbase n1ql database-cursor

share|improve this question

edited Mar 26 at 12:34

Joshua Fox

asked Mar 26 at 10:18

Joshua FoxJoshua Fox

8,6501111 gold badges5252 silver badges8383 bronze badges

add a comment | 

I need to paginate a Couchbase N1ql Query.

I am aware of pagination with OFFSET, but it is more efficient to designate the start and end point.

I see documentation about startkey_docid, but none about how to us this in N1ql.

How do I paginate with cursors, or something similar, in N1ql?

couchbase n1ql database-cursor

share|improve this question

edited Mar 26 at 12:34

Joshua Fox

asked Mar 26 at 10:18

Joshua FoxJoshua Fox

8,6501111 gold badges5252 silver badges8383 bronze badges

share|improve this question

edited Mar 26 at 12:34

Joshua Fox

asked Mar 26 at 10:18

Joshua FoxJoshua Fox

8,6501111 gold badges5252 silver badges8383 bronze badges

share|improve this question

edited Mar 26 at 12:34

Joshua Fox

asked Mar 26 at 10:18

Joshua FoxJoshua Fox

8,6501111 gold badges5252 silver badges8383 bronze badges

asked Mar 26 at 10:18

Joshua FoxJoshua Fox

8,6501111 gold badges5252 silver badges8383 bronze badges

asked Mar 26 at 10:18

Joshua FoxJoshua Fox

8,6501111 gold badges5252 silver badges8383 bronze badges

2 Answers
2

active

oldest

votes

1

Look into "keyset pagination", a general technique for improving pagination performance by leveraging the index instead of using offset. A commonly cited article on the topic is Markus Winand's "We need tool support for keyset pagination."

For a Couchbase-specific example that uses N1QL, see Keshav Murthy's article "Database Pagination: Using OFFSET and Keyset in N1QL."

share|improve this answer

answered Mar 26 at 18:34

dnaultdnault

4,21611 gold badge1919 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Makes sense but what about startkey_docid? It seems like a different approach, also efficient. Is that available for me to use?
  
  – Joshua Fox
  Mar 27 at 13:07
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @JoshuaFox startkey_docid is only for views, not available for N1QL.
  
  – dnault
  Mar 27 at 17:00
  

  
  
  
  

add a comment | 

2

N1QL does not support cursors. Behind the scenes, every query sent to the query engine is a separate HTTP request. There is simply no continuity between queries, and therefore no concept of cursors is supported.

share|improve this answer

answered Mar 26 at 12:04

Johan LarsonJohan Larson

1,70211 gold badge99 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ok, so how do I do pagination (other than with OFFSET, which is inefficient)?
  
  – Joshua Fox
  Mar 26 at 12:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you are going to paginate using the database, OFFSET and LIMIT are your only choices. If that won't do, you'll have to pull the entire result into a higher level of the system (the app server, maybe?) and paginate from there. You can make this second option less heavyweight by fetching doc ids rather than complete documents and filling in the document contents only when they are needed.
  
  – Johan Larson
  Mar 26 at 13:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you . It seems that Pagination is an important feature for pretty much any application. Do you know why only the OFFSET is offered here? Couchbase does have startkey_docid, so clearly the Couchbase designers are aware of this.
  
  – Joshua Fox
  Mar 26 at 15:12
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  It's true that Couchbase does not support cursors, but "keyset pagination" might give better performance than paginating with OFFSET and LIMIT. I put some links in another answer.
  
  – dnault
  Mar 26 at 18:38
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The REST API that supports startkey_docid is very inefficient with large data sets, it's really only good for getting the first thousand or so keys. N1QL can work with "primary indexes" which allow you to search using, "meta().id > x and meta().id < y". Primary indexes should be used with extreme caution, since they can lead to full database scans if you aren't careful.
  
  – EbenH
  Mar 29 at 17:18
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55354659%2fhow-do-i-use-paginate-with-cursors-in-n1ql%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

Look into "keyset pagination", a general technique for improving pagination performance by leveraging the index instead of using offset. A commonly cited article on the topic is Markus Winand's "We need tool support for keyset pagination."

For a Couchbase-specific example that uses N1QL, see Keshav Murthy's article "Database Pagination: Using OFFSET and Keyset in N1QL."

share|improve this answer

answered Mar 26 at 18:34

dnaultdnault

4,21611 gold badge1919 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Makes sense but what about startkey_docid? It seems like a different approach, also efficient. Is that available for me to use?
  
  – Joshua Fox
  Mar 27 at 13:07
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @JoshuaFox startkey_docid is only for views, not available for N1QL.
  
  – dnault
  Mar 27 at 17:00
  

  
  
  
  

add a comment | 

1

Look into "keyset pagination", a general technique for improving pagination performance by leveraging the index instead of using offset. A commonly cited article on the topic is Markus Winand's "We need tool support for keyset pagination."

For a Couchbase-specific example that uses N1QL, see Keshav Murthy's article "Database Pagination: Using OFFSET and Keyset in N1QL."

share|improve this answer

answered Mar 26 at 18:34

dnaultdnault

4,21611 gold badge1919 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Makes sense but what about startkey_docid? It seems like a different approach, also efficient. Is that available for me to use?
  
  – Joshua Fox
  Mar 27 at 13:07
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @JoshuaFox startkey_docid is only for views, not available for N1QL.
  
  – dnault
  Mar 27 at 17:00
  

  
  
  
  

add a comment | 

Look into "keyset pagination", a general technique for improving pagination performance by leveraging the index instead of using offset. A commonly cited article on the topic is Markus Winand's "We need tool support for keyset pagination."

For a Couchbase-specific example that uses N1QL, see Keshav Murthy's article "Database Pagination: Using OFFSET and Keyset in N1QL."

share|improve this answer

answered Mar 26 at 18:34

dnaultdnault

4,21611 gold badge1919 silver badges4040 bronze badges

share|improve this answer

answered Mar 26 at 18:34

dnaultdnault

4,21611 gold badge1919 silver badges4040 bronze badges

answered Mar 26 at 18:34

dnaultdnault

4,21611 gold badge1919 silver badges4040 bronze badges

answered Mar 26 at 18:34

dnaultdnault

4,21611 gold badge1919 silver badges4040 bronze badges

2

N1QL does not support cursors. Behind the scenes, every query sent to the query engine is a separate HTTP request. There is simply no continuity between queries, and therefore no concept of cursors is supported.

share|improve this answer

answered Mar 26 at 12:04

Johan LarsonJohan Larson

1,70211 gold badge99 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ok, so how do I do pagination (other than with OFFSET, which is inefficient)?
  
  – Joshua Fox
  Mar 26 at 12:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you are going to paginate using the database, OFFSET and LIMIT are your only choices. If that won't do, you'll have to pull the entire result into a higher level of the system (the app server, maybe?) and paginate from there. You can make this second option less heavyweight by fetching doc ids rather than complete documents and filling in the document contents only when they are needed.
  
  – Johan Larson
  Mar 26 at 13:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you . It seems that Pagination is an important feature for pretty much any application. Do you know why only the OFFSET is offered here? Couchbase does have startkey_docid, so clearly the Couchbase designers are aware of this.
  
  – Joshua Fox
  Mar 26 at 15:12
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  It's true that Couchbase does not support cursors, but "keyset pagination" might give better performance than paginating with OFFSET and LIMIT. I put some links in another answer.
  
  – dnault
  Mar 26 at 18:38
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The REST API that supports startkey_docid is very inefficient with large data sets, it's really only good for getting the first thousand or so keys. N1QL can work with "primary indexes" which allow you to search using, "meta().id > x and meta().id < y". Primary indexes should be used with extreme caution, since they can lead to full database scans if you aren't careful.
  
  – EbenH
  Mar 29 at 17:18
  

  
  
  
  

add a comment | 

2

N1QL does not support cursors. Behind the scenes, every query sent to the query engine is a separate HTTP request. There is simply no continuity between queries, and therefore no concept of cursors is supported.

share|improve this answer

answered Mar 26 at 12:04

Johan LarsonJohan Larson

1,70211 gold badge99 silver badges1414 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Ok, so how do I do pagination (other than with OFFSET, which is inefficient)?
  
  – Joshua Fox
  Mar 26 at 12:34
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If you are going to paginate using the database, OFFSET and LIMIT are your only choices. If that won't do, you'll have to pull the entire result into a higher level of the system (the app server, maybe?) and paginate from there. You can make this second option less heavyweight by fetching doc ids rather than complete documents and filling in the document contents only when they are needed.
  
  – Johan Larson
  Mar 26 at 13:00
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you . It seems that Pagination is an important feature for pretty much any application. Do you know why only the OFFSET is offered here? Couchbase does have startkey_docid, so clearly the Couchbase designers are aware of this.
  
  – Joshua Fox
  Mar 26 at 15:12
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  It's true that Couchbase does not support cursors, but "keyset pagination" might give better performance than paginating with OFFSET and LIMIT. I put some links in another answer.
  
  – dnault
  Mar 26 at 18:38
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The REST API that supports startkey_docid is very inefficient with large data sets, it's really only good for getting the first thousand or so keys. N1QL can work with "primary indexes" which allow you to search using, "meta().id > x and meta().id < y". Primary indexes should be used with extreme caution, since they can lead to full database scans if you aren't careful.
  
  – EbenH
  Mar 29 at 17:18
  

  
  
  
  

add a comment | 

N1QL does not support cursors. Behind the scenes, every query sent to the query engine is a separate HTTP request. There is simply no continuity between queries, and therefore no concept of cursors is supported.

share|improve this answer

answered Mar 26 at 12:04

Johan LarsonJohan Larson

1,70211 gold badge99 silver badges1414 bronze badges

share|improve this answer

answered Mar 26 at 12:04

Johan LarsonJohan Larson

1,70211 gold badge99 silver badges1414 bronze badges

answered Mar 26 at 12:04

Johan LarsonJohan Larson

1,70211 gold badge99 silver badges1414 bronze badges

answered Mar 26 at 12:04

Johan LarsonJohan Larson

1,70211 gold badge99 silver badges1414 bronze badges