Pagination of API responses

[yesoreyeram]

Available from: Infinity version 2.0.0
Supported types: JSON
Supported parsers: Backend

Pagination

When calling an API, if the underlying system have lots of results typically the server will send responses in multiple pages.

Pagination in Infinity

Introducing pagination of API response in Infinity 2.0. Prior to infinity version 2.0.0, you can only one page at a time. To call multiple pages and consolidate, you might be adding multiple queries manually and use merge transformation.

Types of Pagination

In the modern API world, pagination is implemented in different ways. Following are the common pagination mechanisms supported in infinity

• Offset based pagination
• Page number based pagination
• Cursor based pagination

Offset based pagination

TODO: yet to be documented

Page number based pagination

TODO: yet to be documented

Cursor based pagination

TODO: yet to be documented

Iterating URLs in a loop

Apart from the regular pagination methods mentioned above, From infinity 2.0, you have the ability to iterate the URLs based on comma separated values.

For example, we would like to call 3 different pokemons API items and show in a single result. We will be using following three URL.

• https://pokeapi.co/api/v2/pokemon/bulbasaur
• https://pokeapi.co/api/v2/pokemon/ivysaur
• https://pokeapi.co/api/v2/pokemon/venusaur

As you see, we have three different URLs with 3 different paths. So I am going to to parameterise this as follows.

• https://pokeapi.co/api/v2/pokemon/{{ pokemon_name }}

Now in the infinity pagination, I can instruct it to replace {{ pokemon_name }} part of the URL with bulbasaur,ivysaur,venusaur. Under the hood, infinity will split the values by , and then replace {{ pokemon_name }} with each item in the list and make the API call.

Below screenshot is an example of how to use it.

When used with dashboard variables, you can replace the hardcoded list of items to point a variable. Example: When you have a multi-value variable called pokemon_id, you can refer it as ${pokemon_id:csv} in the list value.

Limitation

Number of maximum pages per query is limited to 5. This is to avoid resource exhaustion and DDOSing of the target system.

[ColdFire87]

@yesoreyeram this looks pretty good and useful. Do you have any date in mind releasing this? Thanks!

[mrdeadlocked]

Is it possible to up the 5 page limit? My REST API currently has a limit of 500, I can set up pagination but I only see 2500 results of the 20,000 we have. Can I increase the limit on my end someplace from this 5 page limit?

[chi-miel]

Hello,
Hello, does this feature also allow pagination in a graphql api ?

In my case, i use body type GraphQL and i need to explicitly indicate page number like :

query {
  list_server(name_search:"server_name",page:1){
    name
  }
}

[yesoreyeram]

@chi-miel - yes you can use. use the replace URL in the field type of your page parameter and then use the corresponding page tokens in the graphql query/post body.

[yesoreyeram]

@rucciva - yes it does. Use the replace URL in the field type.

[ac3xx]

Is pagination via the next page cursor still unsupported? Hitting a similar issue with the GitLab GraphQL API currently.

[dharman0125]

The hard limit of 5 pages max is too low of a limit. Understand trying to protect the source however for large datasets more pages are required.

[yesoreyeram]

Then potentially you are using wrong tool for the job.
What's the use case ?

[dharman0125]

A source tool with a hard limit of 500 datapoints per query and pagenates and I need more then 2500 datapoints.

[vesenthil]

Hi ,
My api will have more then 60000 data .i want to use pagination to retrieve the data in single hit. Which type of pagination is suitable?

[vesenthil]

I used offset pagination and getting below error.

[yesoreyeram]

60000 records

It looks like Grafana infinity is not the right choice for querying such a huge dataset. Anything involve more than 5 API calls / 10000 records, I will consider as huge performance impact. Consider querying aggregated results instead.

[jpike88]

@yesoreyeram with all due respect, I know better what tools to use for the job, I get paid to be good at that.

Instead, I see people here doing the painstaking workaround of making A,B,C queries because you limited your tool's functionality.

If 1 page however is only 10/20 records, that's just 100 records, or 10 records depending on the volume of data being fetched.

In other words please loosen the restriction and let developers make their own decisions.

[CoolZeroNL]

I'm also facing the issue of the hard limit of 5 pages max is too low of a limit. The target API im using has a hard limit of 13 records (dont ask me why) and cant be changed by input. So in this case it will only pull 65 records what is to low because in my case there are more records to process.

[CoolZeroNL]

Regarding the way of paging is working. I have some trouble te find out how to parse nested data when they are been paged. There seems is a differand behaver when the data is collected when using paging, and the inline option.

So when the data is been loaded trough the api, and is paged, we have/see the data returned in the 5 rows.

The nested data we want is that in "hits" from the api, there are 13 items in it. So now i will tell the plugin that the root is: hits and this returned in a error:

logger=plugin.yesoreyeram-infinity-datasource t=2024-02-29T16:18:19.239291243Z level=info msg="performing QueryData in infinity datasource" summarizeExpression= endpoint=queryData dsUID=ac4b8026-c324-4671-8f40-bb410b5372bd filterExpression= source=url url="http://127.0.0.1:9002/" dsName=infinity-datasource-csrf-xsrf-proxy pluginID=yesoreyeram-infinity-datasource root_selector=hits settings.AuthenticationMethod=none settings.OAuth2Settings.OAuth2Type= parser=backend type=json uname=admin uql=parse-json
logger=plugin.yesoreyeram-infinity-datasource t=2024-02-29T16:18:19.655897329Z level=error msg="plugin process exited" plugin=/opt/grafana/plugins/yesoreyeram-infinity-datasource/gpx_infinity_linux_amd64 id=108341 error="exit status 2"
logger=context userId=1 orgId=1 uname=admin t=2024-02-29T16:18:19.655916109Z level=error msg="Internal server error" error="[plugin.downstreamError] client: failed to query data: Failed to query data: rpc error: code = Unavailable desc = error reading from server: EOF" remote_addr=127.0.0.1 traceID=
logger=context userId=1 orgId=1 uname=admin t=2024-02-29T16:18:19.655997872Z level=error msg="Request Completed" method=POST path=/api/ds/query status=500 remote_addr=127.0.0.1 time_ms=432 duration=432.325643ms size=116 referer="https://fdn/d/d94b141c-abd3-4a85-862b-559fd4618ab1/dashboard?editPanel=3&orgId=1&tab=query" handler=/api/ds/query

When i change it to: [hits] i will get the hits, but they will not be extracted as expected.

So i have striped down the json, create a array of 2 and use it as inline data:

[{
    "hits": [
        {
            "id": 1,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "1",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        },
        {
            "id": 2,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "2",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        }
    ],
    "center": {
        "lat": 1.87,
        "lon": 1.45
    },
    "total": 87,
    "pages": 7,
    "page": 1,
    "totalDisplay": 87,
    "description": ""
},
{
    "hits": [
        {
            "id": 3,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "3",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        },
        {
            "id": 4,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "4",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        }
    ],
    "center": {
        "lat": 1.87,
        "lon": 1.45
    },
    "total": 87,
    "pages": 7,
    "page": 2,
    "totalDisplay": 87,
    "description": ""
}]

Whenever i now apply the paring hits the field is as expected selected as root, and the 2 enties are been extracted and showed as seperated rows.

ps, after some hours debugging the json file , seems that there is a strange behaver of it and found that the items in hits are not correct been used as in when there is no number, the API return "" instead of null. Is this something for the pluging to catch an fix presend failing the query ?

{
    "hits": [
        {
            "anumber": "",
            "anumber_base": ""
        },
        {
            "anumber": "121",
            "anumber_base": 121
        }
    ]
}

[wastegum]

Hi, I'm having some trouble using the pagination feature with a json "next" cursor. My API response data is formatted as follows:

{
  "somekey": "somevalue",
  "results": [
    {
      "date": "some timestamp",
      "result": "data I care about"
    },
    {
      "date": "some other timestamp",
      "result": "more data I care about"
    }
  ],
  "_links": {
    "next": {
      "href": "https://myapiurl/myendpoint?cursor=foobar"
    }
  }
}

As you can see, the data I care about is nested inside an array called "results", and I have no problem extracting this data by adding "results[]" in the "Rows/Root" parsing field with the Backend parser.

The reference to the next page exists outside of the results[] array and the API is providing me with a complete URL instead of a "next" marker. It seems that the plugin should support this via URL replacement, but no matter what I try I can't get it to pull in additional pages.

Here is the latest config I have tried:

Any help would be appreciated because without this plugin I'll need to either go write a custom plugin for this data source, or resort to replicating the data elsewhere in a data warehouse which seems like a waste of time and effort.

[jkroepke]

There is an open PR to support this scenario: #784

[cm-slu]

Hello, your solution is great.
Is there a solution to have more than 5 pages?
in my case I have 16 pages but I can have even more

[bahadir-be]

agree!
Can you make more than 5 pages in the new version?
We are using Azure managed grafana solution and we can not edit source code of this plugin :/

[GeoHaz7]

@yesoreyeram hello this is amazing stuff thank you for your work, i have a question thought.

when using Iterating URLs in a loop is there a way to add the variable that i used to loop as a column to have that value in each row to know to which variable value it belongs to?

[benayyad12]

Hello,

I solved pagination problem by adding multiple queries A,B,C for example. in A i used per_page=100 and number page_number 1 , for B per_page=100 and page_number 2. Then I merge using the transformation Merge by Id. I got all elements of page one and two in the same dashboard.

This is without BACKEND parser, it works with UQL

Thank you
Abdessalam BENAYYAD

[frbosch]

Hi, i am trying to use the pagination feature with Azure IoT-Hub. IoT-Hub API uses headers for a cursor:

x-ms-max-item-count request header to set the page size.
X-Ms-Continuation response/request header for the cursor.

I am using this configuration and it does not work:

Do i need to set something in "Extraction path"? What does it extract using "Headers" field type?

Thanks a lot.

[Tukkan]

Correct me if I'm wrong, but I don't really understand the 5 pages limitation. If the data is paginated, why is the number of pages a performance issue?
If number of items is 20, you're fetching always 20 items anyways and the total number of items is usually passed in response data or header.
Thanks

[Walkablenormal]

This has been mentioned numerous times in this thread, but I also believe the '5 pages' limitation should be configurable. Even when examining Grafana's native APIs, such as the Grafana OnCall API, some routes return 50 records per page. The page size is not configurable. In my opinion, a limit of 250 records is far too conservative.

[isaacsanders]

Check out the GitHub GraphQL API for an application in GraphQL for this. It might be nice to give the GraphQL mode access to pagination.

[jpike88]

My eventual solution:

Fivetran + neon postgresql database (both have a free tier)

Works wonders

[jrote1]

There are many cases where the API that's being called is an internal API so it should be down to the developer to choose what the max number of pages is, can the limit be removed from max pages?

[yulipumal]

Also facing the 5 pages limitation problem on an internal API.

[valentintraen]

Same problem with pagination. 5 is very very low