$filter and $orderBy in SharePoint REST requests

In my last post, $select and $expand in SharePoint REST requests, I covered how to use the $select and $expand OData operators in SharePoint REST calls. In this post, I’ll cover the $orderBy and $filter operators.

The Base Query

As in my last post, I’m going to use a base query throughout which I’ll describe once here, and then show how the results are modified by tacking on $orderBy and $filter query parameters. The list I’m going to query is called SalesState, and it looks like this:

It is a pretty simple list. The title field holds the state and there is a SalesDivision field which is a lookup to another list. Also, there is enough data for me to play around with filters and see how it manipulates the results.

As in my previous post, I’m going to be running these queries in the browser console, and my query will use $.ajax, so I need to make sure jQuery is loaded into the page. To do that, I’ll run the following code in the console first, which inserts a script tag into the page that loads jQuery from a CDN:

And the base query is going to bring back the id and title of the item plus the id and title of the SalesRegion lookup field (if you don’t understand the syntax, see my last post). It looks like:

The JSON result from this query looks like:

Nothing earth shattering to see here. $orderBy is quite simple so lets talk about that first.

Using $orderBy to Sort REST Results

The OData specification says that if you run the same query twice, you should get back the same items in the same order unless the underlying data has changed in the interim. It doesn’t say what the order should be if no order is specified, only that it should be determinate. For SharePoint, the default result set order is to sort by Id ascending. This shouldn’t be surprising, since this is the default for all other SharePoint APIs and even for views created in the browser. You specify an alternate sort order with the $orderBy operator.

To sort by Title, simply add the following query parameter:

The result looks like:

You can reverse the sort order by tacking on a space followed by desc (for descending):

I’m not going to show the results, just picture the above results in reverse order and you get the idea. You can also tack on asc (for ascending), but this is obviously the default sort order given the results from my first example.

You can sort on multiple fields by separating them with a comma, so:

will sort by title, and if two items have the same title it will sort them by Id highest to lowest. Below is a pictorial representation of the order by syntax. This is mostly borrowed directly from the Microsoft documentation, but they omitted the loop around with a comma, making it look as though you can only sort by a single field, which is not the case.

Using $filter to Reduce Result Sets

OData $filter provides a high degree of flexibility to limit results to only what you want, saving server processing and network bandwidth and producing all around performance goodness. SharePoint’s OData implementation provide slightly less functionality but it is still quite powerful. It is only a partial implementation of the specification. It’s important to keep that in mind, because the OData specification is often the best source of information but it will describe some functionality that simply isn’t implemented in SharePoint, so you do have to look at the Microsoft documentation as well (which is surprisingly pretty good).

Anyway, I’m just going to try to show the basic $filter syntax right now. As I stumble across clever examples that demonstrate some nuance of OData $filters, I’ll come back and add them from time to time.

So here is a simple OData filter to be tacked onto my query:

The result is an array with only one result, like so:

Now before I go further, I want to mention some stuff about case-sensitivity. First, the query term is not case-sensitive, since I query all lower case and the actual value is title case. This is the same as all other SharePoint APIs. The OData operators (in this case ‘eq’) are, however, case-sensitive. This is particularly unfortunate, as the Microsoft documentation has images which I’m borrowing from to describe these queries, but in their images they’ve labeled all of the operators title case (i.e. ‘Eq’). If you try this yourself in the browser, you will get a query exception. I’ve changed all of the operators to the correct case in my images.

The basic query operators will work as you would expect for both strings and numbers, but if the field is numeric you should not quote the literal value, so:

will yield the exact

same results. Of course, the REST API provides other methods to get an item by Id, which are likely to be more efficient than filtering, so this isn’t the best example.

You can build more complex queries by combining multiple expression with logical operators (and/or), so:

will give you all items with an SalesDivision/Id greater than or equal to 4 and less than or equal to 7. Now you can string together as many of these as you like, but if you’re going to mix and/or operators, you can group them by enclosing compound expressions in parens, like:

If you don’t do that, you could be at the mercy of operator precedence, and I don’t even know what that would be for certain because it’s not mentioned anywhere in the Microsoft documentation. It may be on OData.org or in the Azure documentation somewhere, but I haven’t found it so far. I don’t really care though, because if I’m doing an expression complex enough that I need to worry about it, I’m going to logically group stuff with parens so I explicitly state how I want it resolved.

At this point, I think I’ve accomplished the stated goal of demonstrating the basic syntax for OData filters. So let’s summarize the rules. First, here is a pictorial representation for the basic syntax:

The expression assigned to $filter should evaluate to true or false for each row in the result set. The operators must be lower case, and are:

• eq – is equal to.
• new – is not equal to.
• lt – is less than.
• le – is less than or equal to.
• gt – is greater than.
• ge – is greater than or equal to.

The values may be one of:

• A literal – a literal value. Strings must be single quoted and numbers should not be quoted.
• A field name – must be the internal/static name of a field in the list, in which case the value of the field will be evaluated for each row. Note that if your expression doesn’t contain at least one of these, you’re probably not using filtering correctly.
• A sub-expression – a nested expression, which can be in parens.
• A function call – I may add some examples for this later.

The expression in the above graphic is a simple expression, but $filter expressions can get quite complex. A compound expression, which may or may not be logically grouped in parens, is expressed as:

Summing Up $orderBy and $filter

I’m sure over time I’ll be adding more examples to this post to cover dates and lookups and users and lord only knows what else, but for now, I know what I need to know to start using OData filters.

References

• Use OData query operations in SharePoint REST requests
• OData.org