SharePoint 2013 has a REST API that exposes plenty of information about users, lists and document libraries. For front end developers, this is a gold mine. A gold mine because of two things: 1) SharePoint’s out of the box UI is pretty bad, and 2) SharePoint’s out of the box UI is pretty bad.

So plenty of room for improvement here. Now we can basically recreate SharePoint’s front end: entire new CRUD interfaces and such. And the base of it all – is its REST API.
It is important to recognize that SharePoint REST API is using OData, which is a widely used convention when in comes to RESTful web services. You can learn more about the syntax from their website.
For this post I would like to cover Selecting Items.
Note that I prefer using jQuery’s $.ajax method. So all my calls will look like below:

$.ajax({
   url: url, //THE ENDPOINT
   method: "GET",
   headers: { "Accept": "application/json; odata=verbose" },
   success: function (data) {
        console.log(data.d.results) //RESULTS HERE!!
   }
});

All we’re doing is replacing the url
A special note on SP 2010 users: the querystring section of the examples below may be the same. The only difference I’ve seen is the endpoint part of the url. SP 2010 has below:

"/_vti_bin/listdata.svc/" + querystring

1) Selecting all items, all columns

The simplest way to grab list items is to get everything. This is done by using the endpoint below. Simply change the “list” value to your own:

/_api/web/lists/getbytitle('listname')/Items?$select=*

Again, this will select “almost” all columns that are available to the list.  Note that I said “almost”, because SharePoint’s REST API doesn’t really bring back everything. Things such as author information and values of lookups have to be queried differently (more on this below).

2) Selecting all items, custom columns

)I find it good practice to only grab the columns that I need. This makes the payload as lean as possible.

/_api/web/lists/getbytitle('listname')/Items?$select=ID,Title

This also makes it easier to find things because each Javascript object only contain the columns “ID” and “Title”. What I don’t understand is why “ID” and “Id” is present – maybe a configuration error with the list. But nevertheless, the result is more manageable:

3) Getting the total items in a list

To get the number of items in a list, append the “ItemCount” in the end of your query sting.

/_api/web/lists/getbytitle('listName')/ItemCount

I know that you can simply do a “.length” from the a regular call which brings you all of your results, but the above is useful for filtered results, or when paging. The output looks like below:

4) Retrieving specific list items

Now this endpoint will only get the list item with the ID of “4” (I’ve removed the “_spPageContextInfo.webAbsoluteUrl” component for easier read…

/_api/web/lists/getbytitle('listname')/items(4)

A more powerful solution is using the $filter key. Not that this option allows you to get items with more flexibility. Check out this example which grabs items with the title equals to “example 1”. Note the use of single quotes! Almost all values in SharePoint REST use single quotes!

/_api/web/lists/getbytitle('listname')/Items?$select=Title&$filter=Title eq 'example 1'

5) Multiple $filter keys + values

To get items that have Title that is equal to multiple values, use the endpoint below:

/_api/web/lists/getbytitle('listname')/Items?$select=Title&$filter=((Title eq 'example 1') or (Title eq 'example 2'))

Notice that I used parenthesis to enclose the multiple parameters. This is completely optional – but improves readability.
In the case of multiple “ANDs” and “ORs”, for a stricter and finer result set, you need to use the parenthesis to enclose filter sets. This example below:

/_api/web/lists/getbytitle('ListName')/Items?$&$select=Title&$filter=((ContentType eq 'Operating Policy') or (ContentType eq 'Procedure')) and (Subject eq 'Safety')

You see how we’re grabbing items with “ContentType” equaling to “Operating Policy” OR “Procedure” AND “Subject” equaling to “Safety”.

6) Using “startswith()” and “substringof()” functions with $filter

At times you need a more powerful filter in SharePoint REST API. See “eq” will only retrieve exact matches. If you want to get everything that starts with “test”, you use the “startswith()” function:

/_api/web/lists/getbytitle('ListName')/Items?$filter=((startswith(column,'test')))

Note that the “column” is the first parameter, then the value! This is a mistake that I’ve done several times because the documentation is pretty weak.
Now, if you want to get items that “contains” specific characters, you use the “substringof()” function.

/_api/web/lists/getbytitle('ListName')/Items?$filter=((substringof('test',column)))

Unlike startswith(), the substring() takes the “value” first, then the “column”. Again, note the difference between the two.

7) Limiting your results

The $top parameter basically limits your items to a specified number. The syntax is below:

/_api/web/lists/getbytitle('listname')/Items?$select=Title&$top=10

This is also useful for creating paging systems – in combination with an offset option (below).

8) Offsetting results (for paging)

So you know how to limit your results, and you know how to get the total items. All you need is to offset your results. You do this with the “$skip” parameter.

/_api/web/lists/getbytitle('listname')/Items?$top='10'&$skip='20'

Again, note the use of single quotes. The above will get a set of 10 items, starting from the 20th in the list. Combined with the total, you can create your own paging mechanism.
Update 8/10/2016: I just found out that the $skip parameter doesn’t work with list items. There is a property in the REST response that you can use – for paging. This is known as the “___next”. I will do another tutorial strictly on this subject – so stay tuned.

9) Getting the Author Information

You have to specify that using the “$expand” key. Also an additional parameter to the “$select” key is needed. For example, to grab the Id and the name of the author, simply append to your query:

/_api/web/lists/getbytitle('listname')/Items?$select=Author/Title,Comment&$expand=Author/Title

The above will return the author information in it’s own node called “Author”.

10) Document Libraries

SharePoint REST API supports querying document libraries. Remember, doc libs are also lists and for the most part, are handled through the same endpoint.

/_api/web/lists/getbytitle('Document Library Name')/Items?$select=Title,FileLeafRef,EncodedAbsThumbnailUrl,EncodedAbsUrl

The example above returns information about your file such as the name, full url and the internal “Title”:

For instances where you want to know the “type” of document library it is (example Picture Library vs Document Library), use:

/_api/web/lists/getbytitle('Document Library Name')/BaseTemplate

Although some information regarding document libraries require an entirely new endpoint. This includes file paths, subfolders, thumbnails and such.

/_api/web/GetFolderByServerRelativeUrl('"Folder Name"')/Files

The above contains an entire new set of information regarding your library.

11) Getting Current User information:

At times you want to grab the current user’s information. Use the endpoint below:

/_api/SP.UserProfiles.PeopleManager/GetMyProperties

I’ve also used the more current endpoint below:

/_api/web/currentUser

Again, there may be additional information that is not returned by the above, such as user groups (below) which requires a different endpoint.

12) Getting Current User Groups

For some reason, this information is separate from getting the current user information described above. But in order to get the current user groups, you need to pass the ID:

/_api/web/GetUserById('"+ userId +"')/Groups

So you need to get the “userId” first, then do the above ajax call. Something like below:

function getCurrentUser(){
   var ajax = $.ajax({
       url:   _spPageContextInfo.siteAbsoluteUrl + "/_api/web/currentUser",
       method: "GET",
       headers: { "Accept": "application/json; odata=verbose" },
       error: function (data) {
           console.log(data);
          }
        });
       return ajax;
}
function getUserGroups (userId) {
   var ajax = $.ajax  ({
        url:  _spPageContextInfo.siteAbsoluteUrl + "/_api/web/GetUserById('"   + userId + "')/Groups",
        method: "GET",
           headers: { "Accept": "application/json; odata=verbose" }
     });
    return ajax;
    }
getCurrentUser().done(function(userId){
   getUserGroups(userId.d.Id).done(function(groups){
       console.log(groups.d.results);
   });
});

Above is an example of a “promise”. For more on that, read my Working with jQuery’s AJAX, Promises and Deferred objects.

13) Retrieving”Choice Column” values

There are times when you want to build an application that requires you to drill down to a choice column, and grab it’s values. A “choice column” in SharePoint is a type of column where you add values for users to choose from.

You retrieve the values by doing an ajax call to the SharePoint REST API endpoint, specifying the column name you want. Below is an example:

/_api/web/lists/GetByTitle('LISTNAME')/fields?$filter=EntityPropertyName eq 'Subjects'

The above will query the choice column “Subjects” from the list “LISTNAME”.

**The __metadata object

You will notice that each result will have this __metadata object tied to it.

This contains important material that will help you later in update and delete operations. I’ll cover that in the next chapter.

Bonus: SharePoint Search API

If you have Search features turned on, you will be able to do powerful queries against any of your content inside your SharePoint farm. SharePoint search crawls through your material such as Word documents, Excel, PDF, Lists, Webpages – anything that you’ve configured to be searchable – you can query against in the SharePoint Search API.
Your application will go up an extra level – simply by integrating this capability. Here are a few common Search API calls – for selecting / querying items:

1) A Standard Search

Doing a regular is as simple as passing your search term to the endpoint below:

/_api/search/query?querytext='"+ TERM +"'

The tricky part is parsing the results.

Are you ready for this? The results are buried under: data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results

2) Selecting specific Properties

At times, you want the payload to remain small. The SharePoint Search API supports selecting only the properties you need. Below is a good way to achieve this:

/_api/search/query?querytext='"+ TERM +"'&selectproperties='Title,Path,FileExtension,IsContainer'

Adding a “selectproperties” querystring along with a comma separated properties that we need will do the trick. Again, note the use of single-quotes.

3) Offsetting Results (for paging)

For instances where you want to return the set of rows that starts from a specific number:

/_api/search/query?querytext='"+ TERM +"'&startrow=10

Adding the “startrow” querystring will do it. Note that SharePoint Search API has a complex algorithm of sorting and returning results. Therefore, the total and the actual values are approximates.

4) Limiting Results

You don’t want to return everything! You simply want to limit your search:

/_api/search/query?querytext='"+ TERM +"'&rowlimit=25

The “rowlimit” querystring is your friend.

5) Only Results from a Specific List or Document Library

This one you will need the list Id. You can grab the list Id by going to your list / library, and clicking on “List Settings” or “Library Settings”. Once there, go to the URL and grab the value after “List=”. Note that this value is url encoded – with single quotes. So remove the leading “%7B”, and the trailing “%7D”. What you’re left with is the List Id.

/_api/search/query?querytext='"+ TERM +"'&sourceid='YOUR-LIST-ID'

Remember to wrap your list id in single quotes. This query will only return items from the specific list that you’ve specified.

6) Get People Results

For this one, it’s exacly the same as above.

/_api/search/query?querytext='"+ TERM +"'&sourceid='b09a7990-05ea-4af9-81ef-edfab16c4e31'

Simply replace the list id with this: “b09a7990-05ea-4af9-81ef-edfab16c4e31“. Yes it seems like it’s the same for everybody.

7) Getting Best Bets

“Best Bets” is SharePoint’s “elevated results”. These get pushed to the top of the result set, for specific queries. Note that these items are not included in the regular results – which means that it is not to be included in paging and filters and such.

/_api/search/query?querytext='"+ TERM +"'&processbestbets=true&enablequeryrules=true

The resultset you need to parse is in this property: data.d.query.SecondaryQueryResults.results[0].SpecialTermResults.Results.results
Haha! Ugly eh? Inside “results” is an array of objects you need. Note that the properties of each object may not be as complete as the primary results.

8) Filtering Results

Sometimes you don’t want the whole resultset to comeback. This is where filtering comes in. SharePoint Search API has “refinement filters” which does the job.

/_api/search/query?querytext='TERM'&refinementfilters='(FIELD:string("FILTERTERM", mode="phrase")'

The above will search for “TERM”, filtered with the “FILTERTERM”  in the field called “FIELD”.

9) Filtering Results in Multiple Fields

So now we need to filter by many fields. Yes it’s possible, and we still use “refinement filters” in the Search API.

/_api/search/query?querytext='TERM'&refinementfilters='and(COL1:string("COL1TERM", mode="phrase"),COL2:string("COL2TERM", mode="phrase"))'

Just like the single filter, but instead we add “and” in the beginning (you can also use “or”), plus separating the columns and terms with a comma.

10) Sorting Results

At times, the sorting of SharePoint’s Search API is not what you’re looking for. Well we can sort it.

/_api/search/query?querytext='TERM'&sortlist='LastName:ascending,FirstName:ascending'

The example above shows the results being sorted by “LastName”, ascending first, then “FirstName” etc.

Conclusion

As I’ve mentioned, SharePoint’s REST AND SEARCH API has some very powerful features. And with OData’s standard’s, you can create quite advanced queries. I will try to keep this article updated with more Select related queries, but for now I have to go. Stay tuned for the next part.