Modification of field within nested path (for pagination)

[KrishivGubba]

I'm trying to build a source connector for the Bill Api, and I'm trying to implement pagination in the Airbyte UI, however I'm facing an issue.

This is what the request body to a Bill API endpoint looks like:

{ "data": "{"start":0,"max":999}", "devKey": "xxx", "sessionId": "xxx" }

For pagination, I need the "start" field which is nested within the data object to be modified/incremented (by 999 in this case). Note: I need pagination because the maximum number of records that the endpoint can return in one go is 999 (which is what I've set it to above ^^).

My problem lies in the Airbyte connector builder, wherein you cannot modify a nested field for pagination:

As you can see, the Key Name field only asks for a key and not the path to the key.

By the way, I need the body data to be in URL encoded format, and I need the injected field to be in the same format as well (well, obviously :) ).

Could you please add this feature please. I need to be able to modify the field according the path I provide.

Thank you!

[NAjustin]

@KrishivGubba While it might be nice to allow for this to be done in those settings with paths, there's another way you can accomplish this in Builder today.

First, It's worth noting that the Bill API doesn't actually expect JSON in the body, but rather form-encoded data. So it's really expecting something more like (which is an important distinction on how it needs to be injected):

data=%7B%22start%22%3A0%2C%22max%22%3A999%7D&devKey=xxx&sessionId=xxx

Here's how you can produce this format while using dynamic pagination:

1. Disable the "Inject" options under Pagination (but leave the desired Limit value
2. Scroll up to the Request Body field and change it to Form encoded (key-value pairs) (if it's on another node and anything is already set here, make sure to take note of it since you'll need to re-create it in the appropriate format)
3. Add the keys you mentioned (devKey, sessionId, and data) with their values.
4. In the value for data, replace the static start with this Jinja merge based on the docs: {{ next_page_token.next_page_token|default('0') }}
5. Then do the same for the static limit value, but use {{ config.page_size }}

So the resulting values should look something like this in the case of the example you gave:

• Key: devKey, Value: xxx
• Key: sessionId, Value: xxx
• Key: data, Value: {"start":{{ next_page_token.next_page_token|default('0') }},"max":{{ config.page_size }} }
  

. . . then just merge in whatever additional endpoint-specific options your endpoint requires in data.

It's also worth noting that their API v3 (beta) handles this all in the query parameters, which is much easier. So trying those is another option.

[KrishivGubba]

@NAjustin for sure! Here you go (it made a total of 5 requests, so I'm giving you the first and last one [all requests/pages have the same data])

first req:

'
{
"url": "https://api-stage.bill.com/api/v2/List/Vendor.json",
"body": "data=%7B%22start%22%3A0%2C%22max%22%3A20+%7D&devKey=CENSORED&sessionId=CENSORED",
"headers": {
"User-Agent": "python-requests/2.32.3",
"Accept-Encoding": "gzip, deflate",
"Accept": "/",
"Connection": "keep-alive",
"Content-Length": "154",
"Content-Type": "application/x-www-form-urlencoded"
},
"http_method": "POST"
}

'

Here's what the 5th request it made looked like:
'
{
"url": "https://api-stage.bill.com/api/v2/List/Vendor.json",
"body": "data=%7B%22start%22%3A0%2C%22max%22%3A20+%7D&devKey=xxxxxx&sessionId=xxx",
"headers": {
"User-Agent": "python-requests/2.32.3",
"Accept-Encoding": "gzip, deflate",
"Accept": "/",
"Connection": "keep-alive",
"Cookie": "__cf_bm=; _cfuvid=; csEmulation=false; pageuid=; ptr_locale=; sid=; un_csrf=***",
"Content-Length": "154",
"Content-Type": "application/x-www-form-urlencoded"
},
"http_method": "POST"
}
'

As far as I can see, the "start" field in the URL encoded part of the body is not being incremented correctly. I'd really appreciate your further assistance!

[KrishivGubba]

@NAjustin Hi, wanted to give you an update. Pagination now works, I found a workaround on GH that suggested implementing incremental sync to effectively increment the offset. So I set it up, and it works fine now. Thank you so much for your help.

I can also provide more details about what I set up to fix the issue if you'd like to know :)

[natikgadzhi]

Holy smokes, thank you @NAjustin! We talked on Slack and I couldn't figure it out from the top of my head.

Do you happen to have a working Bill.com connector in Builder? I think a lot of people would love to have it, and I'm looking for a good implementation, API v2 or v3 ;-)

[NAjustin]

@natikgadzhi Unfortunately I don't . . . I just had some remanents still in my head from a one-off pull we had done on a couple endpoints while we were migrating away from them for the old company. Might end up using them in the new company though, so I may be back in the mix on this topic soon 🙃

[KrishivGubba]

@natikgadzhi Hi, I'm working on setting up source connector (for Bill v2) in builder, so I can help with this if you'd like.

I have the List vendors endpoint set up already (with pagination and incremental sync), and replicating the other endpoints is just a matter of duplicating the settings.

[shishank-bsc]

Hi @KrishivGubba I am facing similar problem with another connector (Vinculum).
I have to add three key value pairs in the request body and one of the key value pair is like this :
Key: requestBody
Value: { "orderNo": "", "fromDate": "24/10/2024 00:00:00", "toDate": "25/10/2024 00:00:00", "statuses": [], "pageNumber": "28", "order_Location": "", "fulfillmentLocation": "" }

In the value, there is "pageNumber" field where I want to increment the page number and send the next request.

My response has the number of totalPages and the currentPage. It looks like this:

{
"responseCode": 0,
"responseMessage": "Success",
"totalOrders": 4201,
"totalPages": 43,
"currentPage": 28,
"orderList": []
}

How do I make the pagination work?

Let me know in case you need more details.