$select and $expand in SharePoint REST requests

I have used OData operators like $select, $filter and $expand in previous posts (REST in SharePoint) without any real explanation. This post is the first in a series that will rectify that by giving detailed descriptions of how to use OData operators in SharePoint REST requests. In this post, I’m going to explain the purpose and usage of two OData operators, $select and $expand.

The Base Query

Throughout this post, I’m going to be querying a picture library called ‘Pictures’. Rather than repeat the same query a dozen times, I’m going to go over the basic query here and then show how I can modify it by supplying OData operators as query parameters. 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, which inserts a script tag into the page that loads jQuery from a CDN:

And here is the base query, which retrieves all items from the picture list:

And the result returned from this query looks like so:

It’s just an object with a property called value, which is an array of objects. Each object in the array represents a single item, with a property for each field of the item. This result is not ideal. There’s a lot of crap in there that I probably don’t care about. Like what the heck is a ‘MediaServiceAutoTags’ or a ‘ComplianceAssetId’? In the next section I’ll show how to use $select to limit the fields that are returned to only what I care about. This will reduce the load on the server, the network traffic, and even the load on the browser.

Using $select to Narrow Results

$select is an OData operator. You specify OData operators by tacking a query string onto the end of the request URL, which is just a question mark, followed by one or more name equals value pairs separated by ampersands. The value for the $select parameter is one or more field names separated by a comma. The field names must be the internal name of the field, not the display name. So to limit the results of the base query to just the Title and Description fields, I can tack on the following query parameters:

And the results of this query are:

Isn’t that much better? Now I know that many of you have already stopped reading and will forever more run REST queries in SharePoint bringing back all fields. How do I know this? Because of the 25+ years experience fixing bad SQL code with queries like ‘SELECT * FROM WhatEvs’. It’s a bad idea in SQL, and it’s an even worse idea in SharePoint.

Why worse in SharePoint? Because of the ease with which a non-technical user can change the underlying ‘table’ structure. In other words consider this:

• I write some code on the home page of a site that pulls back all items and fields from a list someplace else and displays them. When I write this code, there were 3 items and 2 fields and everything works swimmingly.
• Everybody in my organization goes to this home page first thing in the morning (you can probably already see it coming).
• Long after I’ve moved on to some other job, some SCA adds 20 fields to the list. Some of them are Notes fields (which can contain up to 65kB each). Also, over time, the list as grown to thousands of items.
• Now the home page takes forever to load, and everybody loading it at once brings the server to its knees. Asked why, the SCA says it’s because my code sucks.

And she’s correct; it is because my code sucks. It’s now bringing back tons of crap that it doesn’t even care about, and it’s throwing it away as soon as it gets it. But along the way, it put a considerable load on the server, the network, and the browser. This is a potential issue even in the SQL scenario, but generally only a technical person could have added all that crap to a table, and if they did that without any thought to how that might affect performance then it’s on them at least as much as it’s on me. But in the SharePoint scenario, you really can’t blame the SCA, they’re just using SharePoint the way it was intended to be used. So in general, limit the width of your results by only selecting fields that you’re going to use or display.

The general syntax for $select looks like the following image. Don’t forget the question mark at the end of the base URL, the dollar sign at the beginning of each OData operator, or to put an ampersand separator between each query parameter or you’ll be scratching your head for a while. So select is pretty simple, but it gets a bit more complicated if you need to pull back data from nested objects, which I’ll get into in the next section.

Using $expand with $select to Return Nested/Deferred Complex Properties

What the heck is a nested/deferred complex property? In general, it’s a lookup of some sort. All items contain lookups. For starters, the Author and Editor of the item are both lookups. In the case of my picture library, the items also have a File property, which is really just a lookup to the Web.Files collection of the current site. And of course, you can add fields that are lookups to other lists.

And why are lookups deferred? You may know that there is a practical limit to how many lookups you can display in a given view in SharePoint before you can expect to run into performance problems. That’s not a front-end limit. When you expand lookups, you’re effectively doing a SQL JOIN. Too many JOINS can lead to huge result sets and bog down SQL server leading to slow results and possibly farm-wide performance problems.

To provide the flexibility to avoid these type of bottlenecks, the back-end doesn’t expand lookups unless you ask it too, which is the purpose of the $expand operator. The following query string will expand the File property:

And the result of this looks like:

Again, I’m not selecting anything, so the service is returning everything. Not just all properties of the items but also all properties of the File. To narrow the results, I need to add a $select, like so:

I’ve added back the same $select value I used in the last section, and now I get back the item Title and Description, but I also get back the File properties with all it’s sub-properties.

To limit the File properties, I need to select something inside the file. For example, to select the File.Name, I can added File/Name to the $select, like so:

And the result, shown below, contains just what I need. Much better!

You can expand multiple lookups in a single query. Here, I’ve added Author to the $expand operator:

And the result looks like:

Um…wait…that doesn’t look right? The problem here is well documented in the Microsoft post Use OData query operations in SharePoint REST requests. You cannot expand something without also selecting something inside of it. Of course, I just did exactly that with the File object at the beginning of this section, and it just returned all properties of the file? That is an undocumented anomaly. In general if you expand something you must select something inside of it or you will get an error similar to the one above. Fixing that by selecting Author/LastName, I get:

And the result looks like:

So the basic syntax for $expand looks like this:

Now here, the value should be an object property. I’ve seen numerous examples where people did things like:

This syntax actually works, but it is not really correct. You expand objects and select primitives. Author is an object property, and LastName and FirstName are primitive properties, so it really should be:

The result is actually the same either way, but the first one contains some redundant nonsense. And remember, of course, that if you expand something, you must also select something inside of it.

One quick note about expanding user fields. When you expand a user field, what you are actually expanding is a lookup to the hidden site collection UserInfo list (/_catalogs/users). The fields that are available to select are the fields that are in this list. And the values are brought in from the user’s profile, but can get stale, and not all values in the profile are available from this list. I also read something, available in the references, that said that if a you query for a value that is not populated, you will get an OData error for a response, but that hasn’t been my experience. It may be dependent on the version of SharePoint you are running on.

Summing Up

That’s all I’ve got for $select and $expand for now. I will add to this post as I discover new things since it’s kind of a reference for me as well.

It’s not that complicated, and I mostly muddled along looking at examples and deducing what works for some time before I started looking at the documentation and formally codifying it into a set of rules. In my next post, I’ll take a look at the OData operators $filter and $orderBy.

References

• Use OData query operations in SharePoint REST requests
• Inconvenient $expand in OData REST query