Summary
This user is seeking guidance on implementing offset pagination in the URL path for a connector. They are looking for assistance in understanding the jinja expression to use for replacing the static value in the URL path.
Question
Hoping somebody here has experience with injecting variables directly into the url path. I was able to find the <Pagination | Airbyte Documentation explaining the use case> I have, but the documentation examples are not clear enough for me to implement successfully. Would LOVE to learn from somebody with experience in this.
The API I am targeting requires pagination, but the pagination is handled through the url path. For example:
/company/{{ config['realm_id'] }}/query?query=SELECT * FROM Account STARTPOSITION 1 MAXRESULTS 50
I need to be able to implement an offset pagination strategy where the 1 following STARTPOSITION in the url path is replaced with some jinja expression. But exactly what expression to use is not clear to me. Has anybody here implemented pagination using this method that could help give some guidance?
This topic has been created from a Slack thread to give it more visibility.
It will be on Read-Only mode here. Click here if you want
to access the original thread.
Join the conversation on Slack
["offset-pagination", "url-path", "jinja-expression", "connector-builder", "pagination"]
<@U035912NS77> You ROCK! This helped address pagination with custom path parameters. THANK YOU.
Is there a good resource that outlines what parameters are available? Finding this solution on my own was not very intuitive.
Sure thing! This isn’t the best bit of the documentation right now, because it involves reading some tea leaves to understand. But the best source overall is the YAML reference page, specifically under <YAML Reference | Airbyte Documentation Variables>.
There you’ll find an example of next_page_token and a note that it has a single key under it also called next_page_token . . . but it doesn’t really explain how it’s set via the Pagination settings (or literally include the word “pagination” at all). Sometimes hovering over some of the 
icons in the Builder UI will also give you some hints.
But there’s also a lot of frustrating cases where different types of configs populate different values. And then there are things that for the life of me I don’t think I can reference (like that Limit property under Pagination—which is why I set it in a user config I use to populate that field, but since it’s set to a numeric input type you have to do it in the YAML and not in the UI 
)
This has been coming up a lot lately, so when I get some time I’m hoping to contribute some docs improvements, especially around next_page_token unless someone else beats me to it.
Well… again. THANK YOU! I’ve been digging into the Official QuickBooks Connector looking at the YAML there and it got me close, but you got me across the finish line on pagination. I’ll check out that YAML reference file as well in more detail. Thanks again.
This should be in {{ next_page_token.next_page_token }}.
So you would set it up to enable Pagination, but NOT insert the limit or offset in the query. Then you would add a Query Parameter named query with a value that looks like this:
SELECT * FROM Account STARTPOSITION {{ next_page_token.next_page_token|default('0') }} MAXRESULTS {{ config['page_size'] }}
(assuming you have a user input named page_size with that value in it; but you could also just hard-code a number there like 100)
This will URL-encode the value, which should do what you want.
Note that the |default('0') filter just gives a fallback for the first page when there isn’t a next page token yet. I don’t know if that number should be 0-based or 1-based, so tweak accordingly