Wednesday, February 9, 2011

Salesforce REST API Query

A couple weeks ago we looked at how to read a record from SFDC using the REST API. The read function has limited usefulness since it requires us to know the internal ID of a record to be able to read it, and in most cases you won’t know this ID. A more useful way of reading records is to use the Query function.
Query allows you to execute Salesforce Object Query Language (SOQL) queries to retrieve a set of records.  SOQL is similar to an SQL SELECT statement. I am not going to go into the details of SOQL, but you can learn more about it here.
The code for doing a Query is pretty much the same as the code I presented in the Salesforce REST API Read Record article, the only difference being the URI. The URI will look something like this:

var uri = instanceURL + “/services/data/v20.0/query?q=SELECT id,accountNumber,name from account

You will see that we have added a query string called q that contains the SOQL query. Note that in SOQL you must specify all the fields you want returned. There is no equivalent of ‘SELECT *’ in SOQL.

The response from the query will look something like this:

"totalSize" : 2,
"done" : true,
"records" : [ {
"attributes" : {
"type" : "Account",
"url" : "/services/data/v20.0/sobjects/Account/0017000000Q9SFkAAN"
"Id" : "0017000000Q9SFkAAN",
"AccountNumber" : null,
"Name" : "Big Account"
}, {
"attributes" : {
"type" : "Account",
"url" : "/services/data/v20.0/sobjects/Account/0017000000O1LiGAAV"
"Id" : "0017000000O1LiGAAV",
"AccountNumber" : "CC978213",
"Name" : "GenePoint"

The first things in the response are two fields called totalSize and done. These are important because there is a limit to the number of record a query can return. My experimentation showed the limit to be 2000 records in this case, but that limit my be different in other situations. The totalSize property tells you how many records met the criteria of the query, even if that total is greater then the record limit. If the total is greater then the done property will be false and there will be an additional property called nextRecordsUrl that looks like this:

"nextRecordsUrl" : "/services/data/v20.0/query/01g7000000FBzMHAA1-2000"

To get the next batch of records you would do another HTTP GET using this URL, and then repeat the process until the done property comes back true.

The actual results of the query will be contained in the records array. Each record will contain a series of attributes, one of which is the URL you can use to read the entire record. Following the attributes will be the fields that you requested in the query.


Dark Knight said...

Hi Dan,

I am facing the same 2000 records limit issue. I was able to read the done atribute which is returned as false and NextRecordUrl but when I use the getMethod object to perform the second query it doesn't work. I end up getting the same result as the first query. Is there any other parameter that I need to set? can you please share java code for this implementation.


Dan Boris said...

Check to be sure you actually are using the NextRecordUrl. I just did a test of this and it works as expected. Sorry,don't know much about Java.