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:
- OData protocol by example
- REST/OData URI Conventions
- Programming using the SharePoint 2013 REST Service.
- OData: Javascript Object Notation (JSON) Format.
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;
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;
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;
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”
Return the count of all list items
Retrieve a list item by it’s ID
Retrieve a list item by it’s ID and return a single property
Retrieve a list item by it’s ID and return a single property value
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.
Using the example above, each list entry returned will include values for the ID, Title and My Document Type columns.
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
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
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
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
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.
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.
This will return a list item where the ID column value is greater than 1.
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.
This will return list items where the MyChoice column has the value ‘Choice2’.
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.
This will return list items in ascending created date order.
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 $filterexpression (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 nentries, according to the $orderby and $skipparameters ~ see Using REST with JSON |
Example.
This will return 2 list items after skipping the first 2 according to the Created date order.
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;
1
2
3
| function onDataReturned(data) {var results = data.d.results;} |
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;
1
2
3
| function onDataReturned(data) {var results = data.d;} |
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;
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;
1
2
3
| 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.
Social Plugin