Not able to fetch all the records from source using restapi url

[Madhan-Rommala]

I attempted to retrieve all of the records using restapi, but the default API url only returns 100 records. Is there a way to get all of the records from the source table?

I tried query parameters such as?limit=200 & $offset=0, but nothing worked. Please see the attached image for more information.

[severussundar]

A single GET response can contain a maximum of only 100 items.

When an entity has more than 100 records, the JSON response will contain a field called nextLink which can be used to fetch the remaining records.

Structure of the JSON response:

{
	"value": [...],
	"nextLink": "..."
}

The nextLink is a completely constructed URI to which a GET request can be executed to fetch the next 100 items.

[yorek]

Pagination is done using the $first and $after parameters: https://github.com/Azure/data-api-builder/blob/main/docs/rest.md#first-and-after

[Madhan-Rommala]

Hi @severussundar/ @yorek -

Thank you for responding so quickly!

In the JSON response, I see "nextLink."in my data, But I'm not sure how I can use this "nextLink" to get the remaining records. I was able to fetch top n records using parameter $first=5.

However, when I try to retrieve all of the records from the source using $first=100 & $after="nextLink". Or $first=100 & $after="xxxxxxx"  getting an error.

It would be great if you could provide an example of how to retrieve all data using rest APIs.

[yorek]

Just remove the double-quotes and you'll be fine :)

[Aniruddh25]

Assuming you are using the version >= 0.5.35...

The returned nextLink in the response is the complete absolute URL that you can use directly to fetch the next page of your first request with the exact same query parameters

For example:

https://localhost:5001/api/Book?$first=2 will provide the first 2 books and the response like:

{
    "value": [
        {
            "id": 1,
            "title": "Awesome book",
            "publisher_id": 1234
        },
        {
            "id": 2,
            "title": "Also Awesome book",
            "publisher_id": 1234
        }
    ],
    "nextLink": "https://localhost:5001/api/Book?$first=2&$after=XXXXXX"
}

As you can see, the nextLink is the complete URL you can use that directly as is to retrieve the next 2 books and so on...

https://localhost:5001/api/Book?$first=2&$after=XXXXXX

Note, the value of $after is an opaque 64 bit encoded string containing the PK after which the next set of items are to be fetched.

[ma20114294]

@Aniruddh25, @yorek -
Is there any example code that demonstrates how to loop through an API when api returned "nextLink"?
Is there any generic approach or sample code for retrieving entire data on many paginations at the same time?

[yorek]

@ma20114294 if you need to get all the items, you can first of all set the $first option to 10000, and then you just use the "nextLink" address to get the next 10000. Here's some code you can run in the browser console:

async function loadItems(url) {
    var items = [];
    var fetchFrom = url;
    while (fetchFrom != null) {
        var response = await fetch(fetchFrom) 
        var payload = await response.json();            
        var result = payload.value;
        result.forEach(item => items.push(item));    
        if (payload.nextLink) {
            //console.log(`loading next item set: ${payload.nextLink}`)
            fetchFrom = payload.nextLink;
        } else {
            fetchFrom = null
        }
    }
    return items;
}

var items = await loadItems('https://localhost:5001/api/todo?$first=1000')

console.log(items.length);