REST SOQL OFFSET and LIMIT – 2000 records

I recently ran into an issue with an Apex batchable class that executed a REST SOQL query performed from SFDC org A against SFDC org B

The batch Apex code in org A was written to handle REST responses that looked like this:

{"totalSize":12,
"done":true | false,
"nextRecordsUrl" : "/services/data/v30.0/query/01g8000001J2eXvAAJ-2000",

"records":[
{"attributes":{
"type" : "the sobject",
"url" : "/services/data/v30.0/sobjects/the sobject/the id"
},
"field0 in query" : "value of field 0",
"field1 in query" : "value of field1",
...},
next record ...
]
}

If "done" : false was present, then, once the iterator reached the end of the retrieved SObjects, it would do an HTTP Request against the value in nextRecordsUrl .

Hence, the batch program could retrieve a large number of records.

And your problem was what?

Turns out my query string used in the original REST GET was of this form:

select id,name from Opportunity where closeDate = THIS_QUARTER Limit 10000 OFFSET 0

Even though there were more than 2000 Opportunities in Org B and even though the coding for the batchable class’s iterator could deal with the queryMore logic, the total number of records retrieved was exactly 2000.

Analysis

Turns out, if you code OFFSET 0, the maximum value returned in the totalSize JSON property of the REST response body is 2000 – regardless of there being more than 2000 rows in the queried org.

Solution
As there was already logic to deal with queryMore(), I simply removed the OFFSET clause. I verified there was no logic to increment OFFSET and repeat the query.

Why was there queryMore() logic in the code in the first place – why not implement LIMIT and OFFSET per SFDC spec?

Turns out, if the retrieved payload from a given query exceeds the maximum transfer size per REST response, SFDC will break up the query results into segments, indicated by the presence of "done": false. These segments will be a maximum of 2000 records but could be (and in my case were – do to payload mass) fewer. The queryMore logic had to be added anyway. Once this was present, the OFFSET portion of the SOQL became superfluous and should have been removed everywhere. Unfortunately, a vestigial OFFSET 0 was present causing ther REST query to only retrieve 2000 rows before saying – I’m done.

Leave a Reply

Your email address will not be published. Required fields are marked *