expand.jpg

Using the $expand OData Query Option in SharePoint 2013

Recently, as part of some of the work for porting the Fab 40 to the new SharePoint 2013 App Model, we needed to expand some of the user information for the currently logged in user.  Sounded simple at first, since I assumed the OData REST query to the SharePoint list would return the information.  Unfortunately, it was not that easy, and the whole SharePoint 2013 OData implementation still seems a bit opaque to me at this point.  Since it is still clear as mud for me, here’s my attempt an an explanation of the $expand query option, and some of the challenges I ran into when using it with SharePoint 2013.

OData Primer

The OASIS OData specification states:

“The OData Protocol is an application-level protocol for interacting with data via RESTful interfaces. The protocol supports the description of data models and the editing and querying of data according to those models.”  In addition there is a description of how the OData protocol differs from other approaches.  “The OData Protocol is different from other REST-based web service approaches in that it provides a uniform way to describe both the data and the data model.”

Fair enough.  Sounds like I should be able to interrogate an OData service(s), query and edit data, and work with the services and data in a very REST-ful way.  I’ll leave reading the complete specification as an exercise for the reader, but I do want to expand (pun intended) on a couple of things to at least explain my findings with SharePoint 2013 and OData.


OData is built on an abstract data model which describes the data which a service provides. The Entity Data Model (or EDM) is the “schema” for a given set of entities and their relationships. For example, Customer and Account entities might be related by AccountId. Note: I have NOT called them lists yet on purpose.   Given the model above, I would  expect to be able to access the User, Customer, and Account entities, and combinations of those entities.  To continue our example above, you can imagine calling the following URLs to return User, Customer and Account entity information respectively.

  • http://host/service/Users
  • http://host/service/Customers
  • http://host/service/Accounts

In fact, if I create Account and Customer lists in SharePoint and call a form of the above, I get something like the following back.  For example, calling

https://company.sharepoint.com/sites/dev_site/_api/web/lists/Customers

in Office 365 yields:

Wait a second… that’s not what I expected!  Well, let’s dig a little deeper.

OData Query Options, Actions and Functions

Based on the OData spec, that URL should work, right?  Not really – trust me read the spec.  If I can’t call the list directly via that URL, what are my options?  If we want to know more about the lists, lets try using the $metadata query option.  To continue our example, since the OData spec indicated that it “provides a uniform way to describe both the data and the data model,” I can find out more about a given entity by calling $metadata.  The spec states the “…Metadata Document URL MUST be the root URL of the service with $metadata appended.”  Well then, calling our previous URL as an HTTP GET request should have worked – I know, I can’t let it go!  Let’s back up a step, literally, and try one of the following URLs.

  • https://company.sharepoint.com/sites/dev_site/_api/web/lists/
  • https://company.sharepoint.com/sites/dev_site/_api/web/lists?$metadata

Now we’re getting somewhere! It looks like the second URL gives us a wealth of metadata and data to consume.

OK, now that we’re finding our way a bit; we obviously need some mechanism for querying, filtering, ordering and managing the data which the service returns.  This is where the Query Options come in.  The standard, expected capabilities to provide filtering, ordering, and paging are:

  • $metadata
  • $select
  • $filter
  • $orderby
  • $skip
  • $top
  • $count

Awesome.  The $metadata query above gives us some clues to how to retrieve information for a given list (see line 5 in the image above).  But how in the world would I know the GUID of list to call _api/web/Lists(guid’xxxx’)?   Well, you really can’t, except with the metadata.  This is where the OData spec affords some extensibility.  Through Actions and Functions a service can provide specialized extension points.  See http://docs.oasis-open.org/odata/odata/v4.0/os/part1-protocol/odata-v4.0-os-part1-protocol.html#_Functions_1.  We found out the actual id of the list (line 5), but that is not very friendly way to access the list itself.  [Note: the image above is elided, but trust me, run that query and review the output, VERY interesting stuff.] 

The short story on the Actions and Functions – Actions can modify data, Functions cannot!  The trick to understanding what Actions and Functions are available from SharePoint 2013 REST seems to be “hidden” in the documentation.

Whenever possible, the URI for these REST endpoints closely mimics the API signature of the resource in the SharePoint client object model. For example:
Client object model method:

  • List.GetByTitle(listname).GetItems()

REST endpoint:

  • http://server/site/_api/lists/getbytitle(‘listname’)/items

That snippet is from the best resource for the SharePoint 2013 implementation of OData I have found: http://msdn.microsoft.com/en-us/library/office/dn292556(v=office.15).aspx#sectionSection1  Bookmark it! Go… Now… Do it!  This page and all the links, combined with the OData spec will help you greatly.  Main take away from that quote should be the use of the Client Object Model methods from REST!  Given that nugget, using the following URLs should now be a bit obvious.

https://comany.sharepoint.com/sites/dev_site/_api/web/lists/getByTitle('Customers')/items
https://comany.sharepoint.com/sites/dev_site/_api/web/lists/getByTitle('Customers')/items?$select=Id, Title
https://comany.sharepoint.com/sites/dev_site/_api/web/lists/getByTitle('Customers')/items?$select=Id,Title&$filter=Id eq '1'

The OData $expand Query Option

Now, given all of the above context, the OData spec provides us with an $expand query option.  The $expand option *should* provide you the ability to retrieve related entities that MUST be represented inline. “Excellent, getting more data from one of more related entities in a single query. Sounds great, but…you knew there was a “but” right?  If you read the OData spec, then you know $expand expects a “comma-separated list of navigation property names, optionally followed by expand options…” (filtering, sorting, etc.)   The end result should mean providing an entity name for expansion and the properties you want expanded.  For example, to expand the Account list columns in the image below:

_api/web/lists/getByTitle('Customers')/items?$select=Id,Title,AccountName&$expand=Accounts

Try it, I dare you!  It won’t work.  Remember I said “entity” earlier and that I did not use “list” on purpose.  Well if you know SharePoint lists, you know you can change field display names, and many other things in List Settings. In the screen shot above, the “Affiliation” columns are columns which are lookups to the Accounts list. I renamed the column name in the Customers list to be “Affiliate”. This is an important point to remember. The “entity” you are referencing here is the “Affiliation” entity, not the “Accounts” entity.  If I call the Account Lookup field “Affiliate” and link by Id, then project the Name and AccountId columns I have to use the following to get the AffiliationId back.

_api/web/lists/getByTitle('Customers')/items?$select=Title,AffiliationId

Next, if you specified the following $expand query option, you would expect to receive the related content “inline” as the spec describes, right?

_api/web/lists/getByTitle('Customers')/items?$select=Title,AffiliationId&$expand=Affiliation

Wrong! Apparently, if you are expanding entities you need to “dereference” (my term) your fields for expansion with a forward slash (/).  Not only do you need to “dereference” the fields, but you need to provide all of the fields you want.

_api/web/lists/getByTitle('Customers')/items?$select=Title,Affiliation/Id&$expand=Affiliation

So, if you have been following along, can you guess if the following will work?

_api/web/lists/getByTitle('Customers')/items?$select=Title,Affiliation/Id,Affiliation/Name&$expand=Affiliation

Silly goose! Of course not, we renamed the Title field to ‘Name,’ so we must refer to this as the InternalName not the display name.  It’s so obvious! How could you have missed that?

_api/web/lists/getByTitle('Customers')/items?$select=Title,Affiliation/Id,Affiliation/Title&$expand=Affiliation

Finally, we see what we expect from the query above (result elided).

{
      __metadata:
          {
               id: "b5fa6848-7b63-4be2-9a1a-e9b2f81166b8"
               uri: "https://company.sharepoint.com/sites/dev_site/_api/Web/Lists(guid'44fba6e3-1be8-41a0-a186-d7c42503ea21')/Items(1)"
               etag: "1"
               type: "SP.Data.CustomersListItem"
          }
          Affiliation:
               {
                    __metadata:
                         {
                                id: "88a23a12-eeba-4178-bf3d-2a66074b8d1f"
                                type: "SP.Data.AccountsListItem"
                         }
            Id: 1
            Title: "ACME,Inc"
        }
  Title: "JBU"
}


Related Posts

Pete SkellyUsing the $expand OData Query Option in SharePoint 2013

25 comments

Join the conversation
  • David Wojcik - April 17, 2014 reply

    Thank you very much. This was exactly what I needed when making an odata against a SharePoint 2013 list item that had a lookup column.

    Amit Lohogaonkar - August 25, 2015

    how to get sharepoint 2013 designer workflow rest api get person column with multiple values and their emails

    ToUsergroup(multi user column in a list)

    I tried $select=EmailBody,Subject,ToUsergroup/EMail,CCUsersGroups/EMail&$expand=ToUsergroup/Id&$expand=CCUsersGroups/Id

    But now how to get these values in next step in designer workflow?

    0801a866-3486-4202-9df0-d57f917f6d452015-08-25T20:33:45Z21f148d0-7fb5-41d6-82a2-fbf98699133c

    KK - June 13, 2016

    Did you find how to get the value into a workflow variable @amitlohogaonkar:disqus

    Amit Lohogaonkar - June 13, 2016

    I had to use dictionary object and for loop inside designer to get all users. It was bit tricky but finally was able to do it! I used users object as well to get user by object name.

    KK - June 14, 2016

    Thanks Amint. Did you loop the multi-lookup field or the all users list, and then get the user name based on his ID?

    Amit Lohogaonkar - June 14, 2016

    I did dictionary loop and then got the user from all users list.

  • jonh - November 3, 2014 reply

    thanks

  • Rich - November 20, 2014 reply

    Hi,
    Thanks for this tips. However, I can’t succeed in putting in place this $expand option.
    Gave it a try with 2 lists :
    A list of work force zones wiht Id/Title/Value (InternalName) (list called SMSGRIG4LSWFZ1033)
    A TEST2 table that has :
    A lookup field called SMSGRIG4LSWFZ1033 on ID, based on the SMSGRIG4LSWFZ1033 list, added a column to show an szcondary field on Value.
    a Field (Number type) called _2014.
    for some reason, it is called OData_2014 internally :
    Here is the odata query :
    ../_api/web/lists/getbytitle(‘TEST2’)/items?$select=SMSGRIG4LSWFZ1033/Id,SMSGRIG4LSWFZ1033/Value,OData__2014&$expand=SMSGRIG4LSWFZ1033
    Unfortnalty I can only see the OData_2014 column…. 🙁
    Any hints/tips to find out why I can’t see the Value extra column.?
    Thanks in advance.

    Pete Skelly - November 21, 2014

    Thanks Rich.
    Based on your information above, I reproduced some lists to test this. Once I reproduced, I simply ran the following to get the actual fields being returned…
    _api/web/lists/getbytitle(‘Test2’)/items(1).

    This returned the field names and meta data (really strange about the OData mangling of the 2014 field!).
    FileSystemObjectType: 0
    Id: 1
    ContentTypeId: “0x0100BB46BFEF6DC24448B813AFBE89830399”
    Title: “Sampel 1”
    SMSGRIG4LSWFZ1033Id: 1
    OData__x0032_014: “Assigned and Active”
    ID: 1
    Modified: “2014-11-21T18:19:29Z”
    Created: “2014-11-21T18:19:29Z”
    AuthorId: 2
    EditorId: 2
    OData__UIVersionString: “1.0”
    Attachments: false
    GUID: “895d6107-92dc-4938-aa2d-13f3124969e3”

    Then using this, I got the expansion for the item using the following…
    _api/web/lists/getbytitle(‘Test2′)/items(1)?$select=Title,SMSGRIG4LSWFZ1033/Value&$expand=SMSGRIG4LSWFZ1033

    This returned with the value expanded properly:
    {
    d: {
    __metadata: {
    id: “e9c5643a-2d2f-4d4d-ab45-1e383ed2379a”
    uri: “http://portal.twlabs.local/sites/expand/_api/Web/Lists(guid’bdfd9bb7-211a-4f19-a1ff-a12989822fbb’)/Items(1)”
    etag: “”1″”
    type: “SP.Data.Test2ListItem”
    }-
    SMSGRIG4LSWFZ1033: {
    __metadata: {
    id: “4db44969-2d59-40df-8c26-1da38e36c357”
    type: “SP.Data.SMSGRIG4LSWFZ1033ListItem”
    }-
    Value: “E-1”
    }-
    Title: “Sampel 1”
    }-
    }

    Not sure what the issue on your end might be unfortunately. The only thing I can think of is be sure to make a call to the “items(1)” portion first before expanding anything to ensure you are getting back expected data. Then add in only the SMSGRIG4LSWFZ1033/Value&$expand=SMSGRIG4LSWFZ1033 portion and test again. HTH

    Pete

  • Marc Dinger - June 3, 2015 reply

    Greetings, Will $expand work with SharePoint Designer 2013 HTTP Web Service Calls? I am trying to load a local variable with the lookup col value using a Get statement without success. Keep getting Error: “The value ‘(0)/ConCat’ cannot be read as type ‘String’. at Microsoft.Activities.GetDynamicValueProperty`1…”. Any assistance is appreciated as I am new to SP Workflows and not being able to get lookup col values using ANY option within SPD 2013 is most frustrating. Thanks! Marc

    Pete Skelly - June 3, 2015

    Marc,

    See reply above. Thought I was replying to your post. 🙂

    Pete

  • Pete Skelly - June 3, 2015 reply

    Marc,

    I woudl expect the $expand should work with the Designer Web Service calls. Be sure to construct the workflow variables properly and use the GetDynamicValueProperty activity for the result. I recommend (if you aren’t already) using Postman or Fiddler or some tool to ensure the query is returning what you expect. Once you have the correct/expected results, then the property name for the data (e.g. data/results/… for whateve the result you wnat is) should be used to retrieve the property. See Andrew Connell’s article http://www.andrewconnell.com/blog/SP2013-Workflow-Dynamic-Values for an example of this.

    HTH

    Pete Skelly

    Marc Dinger - June 5, 2015

    Pete,
    Much appreciate the quick response! Will try your suggestions and see what happens 🙂 Thanks, Marc

  • Amit Lohogaonkar - September 25, 2015 reply

    how to pass another list item in a designer workflow to rest api as a post(copy list item via sp2013!)

    I can create a list item using rest api in designer workflow with fix values. But once I get one list item via get request I want to pass that entire list item to post rest api. I tried application/atom+xml but it gives error. Jason format works but I do not know how to send entire existing list item to post.

    https://social.technet.microsoft.com/Forums/office/en-US/e2f4f562-0e0f-4ff9-a408-e132a07057dc/sharepoint-2013-designer-workflow-copy-list-item-into-same-list-as-clone-using-rest-api?forum=sharepointadmin#e2f4f562-0e0f-4ff9-a408-e132a07057dc

  • Mark Tiderman Jr - June 28, 2016 reply

    Hi Pete, Thanks for the great post. I have a question for you. Do you know of a way to get all the lists and their field names with a single rest call? Thank you again!

    Peter Skelly - June 28, 2016

    Hello Mark,

    I have not tried this specifically but the ODATA spec seems to allow for something close. One option to try would be to specify something like http://host/service/Products?$select=Name,Description&$expand=Category($select=Name). I am not aware of any way to indicate that you want to receive all fields from the related list.

    To see the spec visit http://docs.oasis-open.org/odata/odata/v4.0/errata02/os/complete/part2-url-conventions/odata-v4.0-errata02-os-part2-url-conventions-complete.html#_Toc406398162.

    HTH

    Pete

    Mark Tiderman Jr - June 28, 2016

    Thanks Peter!

  • Larry Seltzer - December 15, 2016 reply

    Hi Peter,

    Thank you, this makes things a bit clearer (clear as mud!). I’m hitting my head against the wall with may or may not be this problem: People. I’m querying a list/entity that has fields which are of type Person or Group. When I query them I get a number, which I imagine is a user key lookup. In fact, I can see if I click on the name in the list that the number is used as a key for userdisp.aspx. But do I use $expand to look up the user name?

    Peter Skelly - December 15, 2016

    Larry,

    If you are querying a Person or Group field, the information comes from the user information hidden list. Just append /_catalogs/users/simple.aspx to the site collection you want the information for to validate the results of the following.

    For example, if you have a Tasks list and you want the Department of the person assigned to the task, the following will pull back the Department (if it is populated).

    Paste the following code into a Script Editor web part on a page, in a web with a Tasks list.

    //<![CDATA[

    $.ajax({
    async: false,
    url: _spPageContextInfo.webAbsoluteUrl
    + "/_api/Web/Lists/GetByTitle('Tasks')/items"
    + "?$select=Id,AssignedTo/Id, AssignedTo/Title, AssignedTo/Department, Title"
    + "&$expand=AssignedTo/Id"
    + "&$top=100",
    type: "GET",
    dataType: "json",
    headers: {
    Accept: "application/json;odata=verbose"

    }
    }).done(function(data, textStatus, jqXHR) {
    var items = data.d.results;

    window.console && console.log(items);
    window.console && console.log(items[0].AssignedTo.results[0].Department);

    });

    When the page runs, open the F12 developer tools or Chrome Dev tools and look at the Console output. The image below are my results. As expected, my Department in the User Information list is “Labs”.

    HTH https://uploads.disquscdn.com/images/9a048a8162439c181b204f05317c7a7de99b2ecaffcd9cfa36c944eb5369ab7a.png

    Peter Skelly - December 15, 2016

    Script got cut off… surround with proper script tag.

    $.ajax({
    async: false,
    url: _spPageContextInfo.webAbsoluteUrl
    + “/_api/Web/Lists/GetByTitle(‘Tasks’)/items”
    + “?$select=Id,AssignedTo/Id, AssignedTo/Title, AssignedTo/Department, Title”
    + “&$expand=AssignedTo/Id”
    + “&$top=500”,
    type: “GET”,
    dataType: “json”,
    headers: {
    Accept: “application/json;odata=verbose”

    }
    }).done(function(data, textStatus, jqXHR) {
    var items = data.d.results;

    window.console && console.log(items);
    window.console && console.log(items[0].AssignedTo.results[0].Department);

    });

    Larry Seltzer - December 31, 2016

    I want to thank you again very much for clearing this up. But I’m pretty sure that when I got this working I only had the list name in the $expand, as in:
    + “&$expand=AssignedTo”

  • Piyush Kumar Singh - May 25, 2017 reply

    Hi Peter,

    Thanks for sharing. I have one question though. Can we not use the OData $expand option in SharePoint App? You see when I use the following query in a SharePoint Content Editor Web Part, I am getting the data as required,
    “targetSiteUrl/_api/Web/Lists(guid’guid’)/items?$select=Number1,Author/Title&$expand=Author/ID”

    However, inside a SharePoint app the following url is not working!

    “https://appUrl/_api/Sp.AppContextSite(@target)/Web/Lists(guid’guid’)/items?$select=Number1,Author/Title&$expand=Author/ID&@target=’targetSiteUrl”

    It seems that may be inside the scope of the app, $expand does not work. But there’s no official doc for it. Also, the fact that $select and $filter works perfectly fine, creates a doubt that probably I am doing something wrong! Plz advice.

  • Arpit - September 5, 2018 reply

    vary nice article. Presentation is entirely different.

Join the conversation

This site uses Akismet to reduce spam. Learn how your comment data is processed.