Inconvenient $expand in OData REST query

Inconvenient $expand in OData REST query

Using REST is a great way to retrieve data in your SharePoint apps. You can easily write it in the browser and review the result of the request. That way you can easily tweak the data retrieval before writing any other app logic. In most of our cases apps rely on retrieving list data of some sort and work with that data. Retrieving user fields can be a bit confusing though.

When retrieving list items you can add a $select to the query to retrieve specific columns:

_api/web/lists/GetByTitle('ListName')/items?$select=Title

However if you want to select a user column (or other lookup columns) you will have to add an $expand as well. By adding the expand you can select the user and some of its properties:

_api/web/lists/GetByTitle('ListName')/items?$select=Title,Members/Name,Members/Title,Members/Id,Members/&$expand=Members/Id

A more extensive guide on the OData operations in REST calls can be found on use OData query operations in SharePoint REST request on MSDN. When extending a user field several user profile properties can be retrieved, but not all. The following user profile properties will be returned:

  • Name
  • Title
  • Department
  • JobTitle

A query that would retrieve all those columns could be:

_api/web/lists/GetByTitle('ListName')/items?$select=Title,Members/Name,Members/Title,Members/Department,Members/JobTitle,Members/Id,Members/&$expand=Members/Id

As you might have noticed the User Profile Properties that can be retrieved are limited. And as it turns out they map the columns that are available in the hidden user info list. Only the columns that are available in the hidden user info list can be queried as that is the source that is used. There is a down side though. If the user profile property is empty it will not be populated in the hidden user info list. This will result in an empty it will not be present in the hidden user info list. This then will result in an exception on the REST call if you would try to retrieve that value:

REST Exception $expand method

So if you are expanding user fields in your REST calls make sure to only requests the field if you are 100% sure that it is filled. If the field is empty in the hidden user info list, or in user profile you can expect an error. You could of course make the user property required to make sure it is filled in and populated in the hidden user info list.

To make sure the field is populated in the hidden user info list you can browse to: /_catalogs/users/simple.aspx

Leave a Reply