world! We are trying to use the NetSuite SuiteQL REST API to pull down data. It works perfectly fine for most of the records we are sourcing, but we hit an interesting snag when we hit large tables.
Since the known limit of a single page of data is 1,000 rows, we are simply calling it with a limit of 1,000 and setting the offsets in 1,000 row increments. Something interesting happens when we get to an offset of 100,000 rows for a record that is larger than 100,000 rows.
If we call https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=98000, we get all the links that we expect.
{
"links": [
{
"rel": "previous",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=97000"
},
{
"rel": "first",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=0"
},
{
"rel": "next",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=99000"
},
{
"rel": "last",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=753000"
},
{
"rel": "self",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=98000"
}
],
"count": 1000,
"hasMore": true,
"items": [
{
[heres my data]...
Setting it to 99,000, a lot of that information disappears. It's almost as if it thinks this is the last page.
{
"links": [
{
"rel": "previous",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=98000"
},
{
"rel": "first",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=0"
},
{
"rel": "self",
"href": "https://myinstance.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=1000&offset=99000"
}
],
"count": 1000,
"hasMore": false,
"items": [
{
[heres my data]...
Setting it to 100,000 flat out gives me an error.
{
"type": "https://www.w3.org/Protocols/rfc2616/rfc2616-sec10.html#sec10.4.5",
"title": "Not Found",
"status": 404,
"o:errorDetails": [
{
"detail": "The specified query parameter 'offset' is out of bounds. Provide value between 0 and 753000.",
"o:errorQueryParam": "offset",
"o:errorCode": "INVALID_PARAMETER"
}
]
Has anyone seen this kind of behavior before? I scoured the documentation and couldn't find any mention of limitation in page offsets, so I'm thinking this might be a bug of some sort (the fact that it even tells you the maximum bounds and it's clearly higher than the offset specified makes me think it's a bug), but hoping someone may have seen this before, and even better, has ideas on how to get around this!
the official documentation for NetSuite REST Web Services mentions this limitation [1]:
Using SuiteQL queries, you can return a maximum of 100,000 results. For more information, see query.runSuiteQLPaged(options).