rest-ft

SharePoint: Adventures with the REST API Part 1


Series Links

Updated 2013-03-19 :: Entity-set Schema. Updated 2013-03-15 :: Using REST with JSON. In this post;

Introduction to REST

REST is one of the client access API’s first introduced in SharePoint 2010. In SharePoint 2013, the REST interface has been hugely expanded and emulates much of the functionality available using the Javascript Client API (JSOM), but everything in this post is still relevant in 2013, since listdata.svc still exists. References:

Using REST

By default the REST API returns data in XML format, which means that you can just enter REST queries into the browser window and see the results. For simple queries this works well, but as you begin to use REST for more complex solutions this quickly becomes annoying because the URL’s start to get quite long. Along with XML, SharePoint REST will also return data in JSON format, which I think will most interest developers, particularly those using the jQuery $.getJSON() function. So, to begin, the base url for all REST calls is made up as follows;

http://server/siteurl/_vti_bin/listdata.svc

If you execute a request using the base url above, you will get back a list of the entity sets found at the service URI; Screen Shot 2013-03-19 at 23.06.56

Entity-set Schema.

The REST API will return a complete schema describing the entity-set collection at the service URI, this is a complex piece of XML (it cannot be returned as a JSON object tree, although the AIR tool does convert it to an object tree in the UI) which decribes the entity-set collection, its types, relationships and a whole lot more. This entity-set schema XML is actually a Microsoft Entity Framework EDMX file and you return it by appending the $metadata keyword after the base url as shown below; Screen Shot 2013-03-19 at 23.18.27

Working with Lists and Libraries using REST.

To work with lists and libraries using REST, you append the list or library display title to the base url.

Note:

List titles in REST are case sensitive and should not include any spaces, so if your list/library is titled “My List Stuff”, the list title you would add to the URL would be “MyListStuff”.

Moving on we will continue by example, showing some typical types of request and the responses.

Return all list items from a list called “REST Test Data”

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData

Screen Shot 2013-03-13 at 23.04.12

Return the count of all list items

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData/$count

Screen Shot 2013-03-13 at 23.06.54

Retrieve a list item by it’s ID

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData(1)

Screen Shot 2013-03-13 at 23.09.18

Retrieve a list item by it’s ID and return a single property

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData(1)/Title

Screen Shot 2013-03-13 at 23.11.11

Retrieve a list item by it’s ID and return a single property value

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData(1)/Title/$value

Screen Shot 2013-03-13 at 23.12.26

Using the $select Query Option to Restrict Returned Columns.

So far the examples have not specified which list columns should be returned with each list entry, by default the SharePoint REST API returns all list entry properties (columns) either when there is no $select query option or when the query option is set to *.

Note:

Unlike JSOM or SPServices, the REST interface requires that you use column Display Names rather than internal names. When specifying a column name you should also ensure that it does not include any spaces, so if your column has a display name of “My Column Name”, the column name you include in the REST URL would be “MyColumnName”. An exception is the ID column, you must refer to this column as Id – for some reason.

Use the $select query string parameter to restrict the returned entry columns, the value of this parameter is a comma separated list of column names as specified above.

http://server/siteurl/_vti_bin/listdata.svc/DocumentsOne?$select=MyDocumentType,Title,Id&$expand=MyDocumentType

Using the example above, each list entry returned will include values for the ID, Title and My Document Type columns. Screen Shot 2013-03-13 at 23.18.12

SharePoint Columns Available via REST.

Not all types of column are available via REST, most annoyingly managed metadata columns are amongst this group of unsupported column types. Further, not all types of column will return data for an entry just by including them in the $select parameter, an example of which is Lookup (single or multi-value) columns.

Column Support Notes
Hyperlink or Picture Supported
Single Line of Text Supported
Multiple lines of text :: Plaintext Supported
Multiple lines of text :: Richtext Supported Returns unencoded XHTML
Multiple lines of text :: Enhanced Richtext Supported Returns unencoded XHTML
Choice Supported Column is required in the $expand keyword
Counter Supported
Integer Supported
Number Supported
Currency Supported
Date Supported Returns an ISO 8601 date e.g. 2013-03-08T11:00:00
Yes/No Supported Returns true or false string literals
Person or Group Supported Column is required in the $expand keyword, append “Id” to the column name to get the user id (UserInformationList) value inline with each entry
Person or Group (Multi) Supported Column is required in the $expand keyword
Calculated Supported
Computed Supported
Managed Metadata Not Supported
Managed Metadata (Multi) Not Supported
Publishing HTML Not Supported
Publishing Hyperlink Not Supported
Publishing Image Not Supported
Media Field Not Supported
Summary Links Not Supported
Publishing Image Not Supported
Lookup Supported Column is required in the $expand keyword, append “Id” to the column name to get the lookup column target id value inline with each entry
Lookup (Multi) Supported Column is required in the $expand keyword

Retrieving list item Lookup or User (Person/Group) column values.

Note:

This section describes returning information from related (Lookup column) list items, but is also relevant for single or multi-value User (Person/Group) columns, in this case the related lookup target list is the User Information List of the site collection.

Lookup columns (single or multi value) must be specified in the $select parameter but this will not return a value for the column. For single value lookup columns only, appending ‘Id’ to the column name causes the REST interface to return the lookup columns lookup ID value.

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData(1)?$select=MyLookupId,Title,Id

Screen Shot 2013-03-13 at 23.22.18 For single and multi-value lookup columns, when just the lookup id is not sufficient and you want the lookup value or some other column from a related list item, add the column name to the $expand query string parameter. The $expand parameter is used to expand related data inline with each entry returned.

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData(1)?$select=MyLookup,Title,Id&$expand=MyLookup

Screen Shot 2013-03-13 at 23.23.53 As you can see this returns the related lookup list item data for each entry, to restrict the property data returned for a related lookup item append the required column name to the source list lookup column name, separated by a / character;

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData(1)?$select=MyLookup/Title,Title,Id&$expand=MyLookup

Screen Shot 2013-03-13 at 23.25.16 This now returns only the Title property for related lookup items for each returned entry.

Retrieving list item Choice column values

Choice columns must be specified in the $select parameter but alone this will not return a value for the column, Choice columns must also be added to the $expand query string parameter. The $expand parameter is used to expand related data inline with each entry returned.

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData(1)?$select=MyChoice,Title,Id&$expand=MyChoice

Screen Shot 2013-03-13 at 23.27.55 As you can see this returns the Choice value for each entry.

Note:

There is an exception to this for Choice columns: when no $select query option has been specified, or it has been set to *, then the Choice column value is returned without having to specify the Choice column in the $expand query option, this can be seen in the image below where the MyChoiceValue property has the value of the Choice column MyChoice – the MyChoice property is still in the deferred state though since it hasn’t been specified in the $expand query option.

Screen Shot 2013-03-14 at 12.37.12

Using the $filter Query Option to Filter Data.

Use the $filter query string parameter to restrict the returned entries, the value of this parameter is the expression used to filter the returned entries.

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData?$select=Title,Id&$filter=Id gt 1

This will return a list item where the ID column value is greater than 1. Screen Shot 2013-03-13 at 23.29.29

Filtering Data using Choice, Lookup or User (Person/Group) Columns.

To filter data using a Choice column, use the $filter query string parameter but append ‘/Value’ to the choice column name used in the filter expression.

Note: This pattern also applies to Lookup and User (Person/Group) columns, except in these cases you can filter by any other columns available on the target lookup/user entry.

Example.

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData?$select=Title,MyChoice&$expand=MyChoice&$filter=MyChoice/Value eq ‘Choice2’

This will return list items where the MyChoice column has the value ‘Choice2’. Screen Shot 2013-03-13 at 23.32.02

Filter Expression Operators.

Expression Usage Notes
Not not x Logical not
And x and y Logical And
Or x or y Logical Or
Multiply x mul y Multiplication operator, e.g. Id mul 2 eq 4
Divide x div y Division operator, e.g. Id div 2 eq 4
Modulo x mod y Modulo operator, e.g. Id mod 2 eq 0
Add x add y Addition operator, e.g. Id add 1 eq 2
Subtract x sub y Subtraction operator, e.g. Id sub 1 eq 2
Less x lt 10 Less than operator, e.g. Id lt 10
Less or equal x le 10 Less or equal operator, e.g. Id le 10
Greater x gt 10 Greater than operator, e.g. Id gt 10
Greater or equal x ge 10 Greater or equal operator, e.g. Id ge 10
Equal x eq 10 Equals operator, e.g. Id eq 10 or Title eq ‘My Title’
Not equal x ne 10 Not equals operator, e.g. Id ne 10 or Title ne ‘My Title’

Filter Expression Literal Values.

Examples.

http://server/siteurl/_vti_bin/listdata.svc/MyList?$filter=Title eq ‘Some Title’&$select=ID,Title http://server/siteurl/_vti_bin/listdata.svc/MyList?$filter=Title ne null and Title ne ”&$select=ID,Title

Literal Description
String A string literal, e.g. Title eq ‘MyText’
DateTime A datetime literal, e.g. datetime’2013-03-12T12:01:38Z’ or datetime’2013-03-12′
GUID A GUID literal, e.g. guid’1225c695-cfb8-4ebb-aaaa-80da344efa6a’
Null A literal token used for comparison with null, e.g. Title ne null
Boolean Boolean literal values, e.g. MyYesNo eq true | false

Filter Expression Methods.

Examples.

http://server/siteurl/_vti_bin/listdata.svc/MyList?$filter=startswith(Title,’Some Title’)&$select=ID,Title http://server/siteurl/_vti_bin/listdata.svc/MyList?$filter=length(Title) gt 0&$select=ID,Title

Method Usage Notes
endswith endswith(x,y) indicates whether the first parameter ends with the second parameter (case-insenstive), e.g. endswith(Title,’1.1′)
indexof indexof(x,y) returns the zero-based index of the second parameter within the first parameter, e.g. indexof(Title,’1.1′) eq 0
startswith startswith(x,y) indicates whether the first parameter starts with the second parameter (case-insensitive), e.g. startswith(Title,’1.1′)
tolower tolower(x) returns the first parameter converted to lowercase, e.g. tolower(Title) eq ‘test’
toupper toupper(x) returns the first parameter converted to uppercase, e.g. toupper(Title) eq ‘TEST’
trim trim(x) returns the first parameter with all leading/trailing spaces removed, e.g. trim(Title) eq ‘Test’
substring substring(x,y) returns the substring of the first parameter starting at the zero-based index of the second parameter, e.g. substring(Title,5) eq ‘Item 1’
substringof substringof(x,y) returns true if the second parameter contains the first parameter, e.g. substringof(‘test item’,Title)
concat concat(x,y,z) returns the concatenation of the parameters in the order listed, e.g. concat(‘A:’,Title) eq ‘A:Title’
length length(x) returns the length of the first parameter, e.g. length(Title) gt 10
year year(x) returns the year component of the first parameter, e.g. year(Created) eq 2013
month month(x) returns the month component of the first parameter, e.g. month(Created) eq 12
day day(x) returns the day component of the first parameter, e.g. day(Created) eq 31
hour hour(x) returns the hour component of the first parameter, e.g. hour(Created) eq 12
minute minute(x) returns the minute component of the first parameter, e.g. minute(Created) eq 20
second second(x) returns the second component of the first parameter, e.g. second(Created) eq 30
round round(x) returns the nearest integral value of the first parameter, e.g. round(NumColumn) eq 1
floor floor(x) returns the largest integral value less or equal to the first parameter, e.g. floor(NumColumn) eq 1
ceiling ceiling(x) returns the smallest integral value greater or equal to the first parameter, e.g. ceiling(NumColumn) eq 1

Using the $orderby Query Option to Sort Data.

Use the $orderby query string parameter to order the returned entries, the value of this parameter is a comma separated list of column name, each column name can be followed by either asc or desc to determine ascending or descending order.

http://server/siteurl/_vti_bin/listdata.svc/RESTTestData?$select=Title&$orderby=Created asc

This will return list items in ascending created date order. Screen Shot 2013-03-13 at 23.37.51

Paging Data and the $top, $orderby, $skip and $inlinecount Parameters.

Adding the $inlinecount=allpages query string parameter causes a __count property to be added to the results which indicates the total number of entries which match the $filter expression (if one was supplied, otherwise it is the total item count). This is particularly useful when using the $top, $skip and $orderby parameters.

Parameter Usage Notes
$skip $skip=n Returns entries skipping the first n entries, according to the $orderby parameter
$top $top=n Returns only the top n entries, according to the $orderby and $skip parameters ~ see Using REST with JSON

Example.

http://server/siteurl/_vti_bin/listdata.svc/TestList?$select=Id,Title&$orderby=Created&$inlinecount=allpages&skip=2&$top=2

This will return 2 list items after skipping the first 2 according to the Created date order. Screen Shot 2013-03-13 at 23.41.44

Using REST with JSON.

While working with the REST service, in particular when returning JSON results using jQuery.getJSON(), there are some quirks to watch out for;

Using the $top query option.

When you don’t include a $top query option, the JSON results are returned to your completion function, typically as follows;

function onDataReturned(data) {
var results = data.d.results;
}

Screen Shot 2013-03-15 at 15.39.23 If you do include the $top query option, the results are returned in the d property as an array, unless you also include the $inlinecount=allpages query option;

function onDataReturned(data) {
var results = data.d;
}

Screen Shot 2013-03-15 at 15.40.38

Dates are returned in Edm.DateTime format.

DateTime columns returned by the REST service in JSON format are encoded using Edm.DateTime format, as shown;

Screen Shot 2013-03-15 at 15.40.07

The numeric value is the number of milliseconds since midnight Jan 1, 1970, you can convert this to a javascript Date object using the following code;

String.prototype.fromEdmDate = function() {
return new Date(parseInt(this.match(/\/Date\(([0-9]+)(?:.*)\)\//)[1]));
};

Summary.

The SharePoint 2010 REST service is quite a powerful feature but due to lack of documentation it can take some getting used to, and quite a good deal of prodding and poking. Data-access via REST or SPServices fits more naturally with other client-side development technologies (jQuery, Backbone, Knockout, jsRender et al) in a way that isn’t so with straight JSOM, and whats more using REST with jQuery allows you to use Promises as a way of combining or building dependencies between your data-access requests, again something that isn’t really possible with JSOM [well, it is possible but its damn ugly and quickly becomes unmanageable]. Throughout this article you may have noticed that I was using a REST user interface which showed tag clouds of REST keywords and column names and also showed the results of REST calls as a JSON object tree. This is a tool I’ve developed to help me become more productive with REST and to help me learn and understand the SharePoint REST API better. In part 2 of this series I’ll show some more about this tool and elaborate on some of the development technologies I used, most of which I’ve already mentioned. While developing this I also took the opportunity to learn Javascript [the Cockroach of Languages – I saw this on twitter somewhere, I forget by who but I’m stealing it] a lot better than I did, since client side development seems like it will have much more prominence in SharePoint 2013, the pain seems worth it.

Published by

Phil Harding

SharePoint Consultant, Developer, Father, Husband and Climber.

43 thoughts on “SharePoint: Adventures with the REST API Part 1

  1. Thank you for the excellent post.

    I was wondering if you could help as I am having trouble in constructing the URL to filter SharePoint 2010 Announcement list items that have not expired. Here is what I have but I couldn’t find a way to solve it as there seem no way of handling Today’s date:

    http://%5BMy SP 2010 site]/_vti_bin/listdata.svc/Announcements?$select=Title,Created,Expires&$filter=Expires ge (Today)

    Thanks

  2. Starkey/

    When using the REST interface there is no ‘Today’ token as there is for CAML, you will have to use a date literal in your filter expression, e.g. Expires ge datetime’2014-06-11′

  3. Phil,

    Thank you for your amazingly fast response.

    It is disappointing that there is no token to handle such scenario. That means I cannot create a view to show unexpired announcement list items using the REST API as I do not want to hard code the date.

    Once again thank you and good luck with the England soccer team this Saturday!!

    Starkey

  4. As you suggested, I used the below URL construct but I am getting the error, “Operator ‘eq’ incompatible with operand types ‘System.Nullable`1[[System.DateTime, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]’ and ‘System.String’ at position 8.”

    http://%5BMy site]/sites/res/team/_vti_bin/listdata.svc/Announcements?$select=Title,Created,Expires&$filter=Expires eq ‘”+new Date().toISOString().slice(0,10)+”‘

  5. Ram/

    Combine filter expressions using the and/or keywords.

    A datetime filter should be written like this;

    MyDateTimeColumn ge datetime’2014-09-01′ and MyDateTimeColumn le datetime’2014-09-30′

    BTW, Its important that there is no space between the single quote and the datetime keyword.

  6. Thanks Phil that worked well. But i soon ran into a problem

    I was using Filter below
    $filter=((Modified ge DateTime’2014-09-01′) and (hour(Modified) ge 1) and (minute(Modified) ge 0) and (second(Modified) ge 0))&top=2

    It gave me an error message “An error occurred while processing this request”
    I have records whose Modified dates are in month of August that meets the criteria i gave. And the same filter worked 3 days before.

    But when i filtered with the DateTime’2014-09-01′ it worked fine.

    I am not knowing why is this happening. It seems to work only of the date value in the filter is in current month.

    Any thoughts on this please

  7. Edit for above post
    i used top paramter with ‘$’ as added only
    $filter=((Modified ge DateTime’2014-09-01′) and (hour(Modified) ge 1) and (minute(Modified) ge 0) and (second(Modified) ge 0))&$top=2

  8. Ram/

    No I couldn’t get the hour/minute/second functions to work correctly, I used DateTime expressions such as below which did work

    $filter=Modified ge DateTime'2014-09-03T00:00:00' and Modified le DateTime'2014-09-30T23:59:59'

  9. Hi Phil Thank you.

    the datetime filter doesn’t take time value in to account
    Modified ge DateTime’2014-09-01T12:00:00′ and
    Modified ge DateTime’2014-09-01T07:00:00′ gives the same result. Somehow time values are not taken into account for filtering. So I am using hour, minute and second

  10. Hi Phil,

    Can you help me out again please

    I am getting an error “An error occurred while processing this request.” whenever I am using
    fitler=(Modified ge DateTime’2014-09-07′).

    But if I use a future date filter like
    fitler=(Modified ge DateTime’2015-09-07′) i am not getting any error and getting the response with no records which is right.

    Could you please share your thought why there is an error if we are using old or current date/time

    Appreciate your kind support

    -Ram

  11. Can you please provide an example for lookup field for “people or group” type? For example a simple list with AssignedTo column. I need to return user name. my query looks like

    $select=Title,AssignedToId/Name&$expands=AssignedToId/Name

    It does not take AssignedTo, it works with AssignedToId. I figured out by looking at full response from
    _api/Web/Lists/GetByTitle(‘mylist’)/items and i appending the above select after this URL to test in browser if i get what i need. It keep returning me Id instead of user name in assigned to field.

  12. Thanks for the great article. It is very informative.

    Is there a way to get the list of sub-sites of a site in Sharepoint 2010 through REST api?

  13. Hi, thank you for the reply..

    I don’ see any REST api for search either in Sharepoint 2010. Please correct me if I got it wrong..

  14. we were doing it in office 365. However it did not work. We had to use a workaround of creating a single line of text field and storing assigned to value using designer workflow. We were then able to use it in REST call.

  15. Tricky thing is that in “JSON Edm.DateTime format” like ‘/Date(123456789)/’ there is an info up to milliseconds, but when getting date-time via $value and obtain “string Edm.DateTime” like ‘2015-03-27T13:30:27’ SharePoint service gives no milliseconds (though, Edm.DateTime allows it). i.e. the precision is lost.

  16. The best one with enough details to understand what’s going on there, thanks saved a lot of time.

  17. Hi Phill, thanks for the excellent post!
    Can you please kindly post an example of how to access multiple values of a lookup table?
    I used ‘expand’, which successfully gives me the resuls, the thing is that I have a field ‘PostCategory’ which accepts multiple values of a lookup table; so PostCategory comes back as an array.
    First, I loop through the number of itmes in “d/results”, and I access each item by creating a dictionary
    from: d/results([%Variable:Index%])
    so, for accessing the ‘PostCategory’ values my logic is to
    a) create another dictionary from: d/results([%Variable:Index%])/PostCategory/results
    however, when I count items i get 0; meaning, I’m incorrectly accessing the PostCategory items.
    {“d”:{
    “results”; [ {“__metadata” – bla, bla, bla,
    “PostCategory”: {“results”: [ {“__metadata”: – bla, bla, bla,
    “Title”: “Announcements”},
    {“__metadata”: – bla, bla, bla,
    “Title”: “Policies”}
    ]
    },
    “Author”:”Theo”,
    “Id”: 59,
    “Title”: “Coming to play”
    }
    ]
    }
    }

    here is my rest call
    _api/Web/Lists/GetByTitle(‘Posts’)/Items?$select=Id,Title,Author/Id,Author/Title,Author/UserName,PublishedDate,PostCategory/Title&$expand=Author,PostCategory

    Any idea on what I’m missing/doing wrong??

    Thanks,
    Laura

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s