Understanding SharePoint’s REST and Search API Part 1 – Selecting Items

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.

Update 3/2017: I’ve added Search API to this post as well. Continue reading below.

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:
rest-select-1

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:
item-count1

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”.
rest-select-2

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”:
rest-select2
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.
Choice column
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.
3rd-screenshot-rest
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.
Search API result
Are you ready for this? The results are buried under: data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results

The “results” property of “Rows” is an array of objects. This contains the data you want to display.

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.

35 Comments

  1. When I run the queries above (namely the all items all columns) the object that is returned in “data” is a Document Object, and it appears to be the entire page’s content. Does this mean I did something wrong?
    My endpoint string is identical with your except for the list name:
    `_spPageContextInfo.webAbsoluteUrl + “/_api” + “/web/lists/getbytitle(‘myTestList’)/Items?$select=*”;`

    Reply
    • Make sure you have all the ajax properties setup:

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

      the data object should have d.results – which is another object.

      Reply
      • Hmm. Bizarre. I don’t know what’s different between what I had and what I have now, but it’s returning a data object with a ‘d’ property.
        Thanks

        Reply
  2. Thanks for the article, this is a nice reference. It says Part 1 in the title, but I can’t find a Part 2, is there one on inserting or updating?

    Reply
  3. Hello Michael my name is Jorge and I want to consult you about a query, I have a list in sharepoint that has n categories I want for each category I generate your own consecutive code, I hope you can help me, thanks

    Reply
  4. Hi ! I am getting below error in console after I embed the $.ajax for fetching data from sharepoint url:
    “`
    blocked by CORS policy:
    No ‘Access-Control-Allow-Origin’ header is present on the requested resource.
    “`
    The url I am trying to hit is behind my organization’s SSO, and I am already logged in with SSO when I am making the ajax request.
    Can you please tell what am I doing wrong ?

    Reply
    • You are trying to access data from a different website, even if it’s in your organization and you’re using SSO. You still need CORS in that server by setting “Access-Control-Allow-Origin” to “*”. You also need to set “Access-Control-Allow-Credentials” to “true”.
      Lastly, in your ajax call you will need to add:
      xhrFields: {
      withCredentials: true
      }

      Reply
      • Thanks a lot for your response!! In the meantime, I also tried to send the XHR (XMLHttpRequest()) by creating a CORS request. Now my XMLHttpRequest() has “withCredentials” as true, but I am still getting
        –No ‘Access-Control-Allow-Origin’ header is present on the requested resource. After I do xhr.send() (where var xhr = new XMLHttpRequest() ), it goes to xhr.onerror() rather than going to xhr.onload()
        So, I guess all arrows point to setting “Access-Control-Allow-Origin” to true on the server. But unfortunately, I don’t think that I’ll be able to convince the IT/Infosec to change the settings on the server-side. Is there any possibility of a work-around ?
        Once again, thanks a lot for your time to understand my query as well as for writing this tutorial.

        Reply
  5. Great post …
    how could I get (return) all pages in a subsite that contains (NOT equals) string “loren” in the title filtering by content type Topics for instance.
    Thanks so much,
    Fábio

    Reply
  6. Hi Michael,
    Need your help –
    I’m trying to get the list of all PDF files under library folder name ‘Submitted after 2017-06-13’.
    Library name: BillsOfLading
    Folder name under Library BillsOfLading: Submitted after 2017-06-13
    I’m using SOAPUI client to access the SharePoint 2010 Rest API.
    Question: Is the below syntax correct?
    http:///_vti_bin/ListData.svc/BillsOfLading?$filter=Path eq ‘/Submitted after 2017-06-13’
    Appreciate your help. Thank you
    Carlo

    Reply
    • Good question. “Most Trending” as in “likes” or “most viewed” – you’d have to look for that in the Search API documentation.

      Reply
      • I want to get my all most viewed documents, i found some properties “ViewLifeTime” and “ViewRecent”, well it is working but m still not getting proper results.
        var query = “/_api/search/query?querytext=’path:\””+ _spPageContextInfo.webAbsoluteUrl+”\” ContentTypeId:”+ContentTypeID+”* -ContentTypeId:0x0120* NOT(ViewsLifeTime:0) ‘&selectproperties=’Path,Url,DocId,Title,Filename,FileExtension,FileType,Created,CreatedBy,CreatedOWSDATE,ModifiedBy,ModifiedOWSDATE,LastModifiedTime,Size,ViewCountLifetime,ViewsLifeTime,ViewsRecent’ &sortlist=’ViewsLifeTime:descending’ &rowlimit=50000 &refinementfilters=’displayauthor:equals(\”” + userName + “\”)'”;
        Please help me whats is wrong into it.

        Reply
  7. Micheal,
    you didn’t elaborate about getting results using two lists. The main list with a lookup field Countries and the list Countries with the fields CountryName and CountryCode. I need to get the CountryCode which is associated with my Country in the main list.
    Thanks
    Marco
    Netherlands

    Reply
    • Using two lists as in Master Detail? That’s a very good question and really wasn’t scoped for this article. Master detail from SharePoint lists is an entirely new tutorial, which I will write about soon.

      Reply
  8. hi i found a article much help ful , but i m stuck in one part
    my req is to find all document on which user has permission but,
    using this “_api/search/query?querytext=” i m getting all documents irrespective of permission but,
    if i fire this “web/lists/getbytitle(‘Test’)” i cant see my document as i have implemeted co autoring permission,
    can u pls give me some hint so i can find out the way to get it worked

    Reply
  9. /_api/web/lists/getbytitle(‘ListName’)/Items?$filter=((substringof(‘test’,column)))
    This will work only for Single line column type and not for multiple line column
    The field ‘FIELD NAME’ of type ‘Note’ cannot be used in the query filter expression.

    Reply
  10. I have a choice column and many other column with single lines of text .
    based on other column i want to get choice column and bind to dropdown .
    supppose equipment no are : 1 , 3 , 2
    for eq no 1 : choice column value are : apple , mango , banna
    for eq no 2 : choice column value are : brinjal , cabbage
    for eq no 3 : choice column value are : red, green, blue
    so i want to give a rest call based on eq no . and bind the choice column to dropdown . help plz

    Reply
  11. Hi Michael, great article. I’m trying to filter a query based on the properties in the ‘__metadata’ block (I’m interested in the version, so I’m looking at ‘etag’).
    I’ve tried using it with $filter as I have done for other keys, but to no avail: $filter=((etag eq ‘2’)
    Any ideas?

    Reply

  12. 1) SharePoint’s out of the box UI is pretty bad, and
    2) SharePoint’s out of the box UI is pretty bad.”
    I belive you accidently copied and pasted the first argument as a second argument for SharePoint REST API being a gold mine for front-end developers.
    So, what was the second argument?

    Reply
  13. Hi,
    Nice artical,
    However I am stuck in searching list items from specific list.
    I have query like you mentioned above it also fetvh searched data but I cant find my lists items in data.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results ,
    I need to display search items in HTML table but no list data found in above results.
    Please help.

    Reply
  14. Hi Michael.
    This article has stood the test of time!
    I am using the SharePoint Search Rest API but I want to return the file ID (not Document ID) in my search results – is this possible at all?

    Reply

Leave a Reply to Stas Cancel reply