using pagination to pull data from a REST api

[smeetsh]

Hi All,

I am using a pipeline in fabric to pull data from an API. In itself a simple task but the API is paginated and I can't get the copy activity to cycle through it correctly.

my relative url to pull the data is: /api/v1/livestock-events?page={page}. As a pagination rule I have set: header as a range, which starts at 1, has no end and an offset of one.

I can't find what i need to set as end condition.

The json from the API has some meta data that indicates things like current_page and last_page What is the expression for the end condition? The main problem is I cant hardcode the end of the range, since that will vary over time.

The json metadata looks like:

"meta": {

        "current_page": 2,

        "from": 3,

        "last_page": 159,

        "path": "https://go.mytrev.com/api/v1/livestock-events",

        "per_page": 2,

        "to": 4,

        "total": 318

    }

Thanks Hans

[smeetsh · Accepted solution]

I managed to sort it out (in this scenario anyway). In the json response there are links to "next page"

In my json array 

there is an array named "links" and in that array is a key/value pair "next" The key  "next" stores the actual link to the next page, and if there is no more next page it will have a value null.

As an example:

-----

"links": {

        "first": "https://myurl/api/v1/events?page=1",

        "last": "https://myurl/api/v1/events?page=22",

        "prev": null,

        "next": "https://myurl/api/v1/events?page=2"

    },

-----

If the api response has this (and from what I can gather it is actually a standard), the solution for pagination is very simple. Under the pagination rules you have to enter the following

The thing I couldn't figure out (and copilot completely missed it) is that you use AbsoluteUrl, you leave the next column blank and under value you choose body with that value in my case links.next. Links comes from the array name "links",  and "next" is the key that holds the url for the next page, and if there is no more next page it becomes null, and the copy activity will end.

As another example: if the array name had been pagination and the key next_page, then the value would have been "pagination.next_page"

I hope this helps others to handle pagination. If your json response does not have an array  for pagination like mine had. It really becomes dependant on what the API is capable of and that could mean some how storing the last page value in in variable and looping through it.

Cheers

Hans

[v-pbandela-msft]

Hi @smeetsh,

Thank you for reaching out in Microsoft Community Forum.

Please follow below steps for Dynamic Pagination;

1.Use a Web Activity to call the API for page 1.

2.Extract last_page from the response (body.meta.last_page).

3.Store last_page in a pipeline variable and Use a ForEach Activity that loops from 1 to last_page.

4.Inside the loop, use a Copy Activity to fetch data for each page (/api/v1/livestock-events?page=@{item()}).

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[smeetsh]

Thanks for this, I had thought about looping and this sounds like a good way forward but how do I extract that value from the response? Would that be done by creating at table in my lakehouse map that value to a column in there? And then use a loop, reading that one specific value form the lakehouse table. How would I increment the value from one to last page though?