44

In odata v4.0 is there an option for combining $expand and $select together?

I have a scenario wherein I'm trying to get specific columns in productItemChoices and item. The below query will give you all the columns in productItemChoices. I only need one column in the productItemChoices

odata/Products(08f80b45-68a9-4a9f-a516-556e69e6bd58)?$expand=productItemChoices($expand=item($select=name))
Boghyon Hoffmann
  • 17,103
  • 12
  • 72
  • 170
pranag
  • 5,432
  • 2
  • 17
  • 15

2 Answers2

75

After going through a lot of time on this, I finally got the answer. We can nest select within expand using ; as a separator, something like

odata/Products(8)?$expand=choices($select=col1,col2;$expand=item($select=name))

This is documented in the OData v4 $expand documentation. The documentation also lists other useful examples such as

Categories?$expand=Products($filter=DiscontinuedDate eq null)
Categories?$expand=Products/$count($search=blue)
erdomke
  • 4,980
  • 1
  • 24
  • 30
pranag
  • 5,432
  • 2
  • 17
  • 15
  • 1
    Works! Any source documentation for this? – Jerther Dec 16 '15 at 17:28
  • That DOES work. Which is funny because my docs (asp.NET WebAPI) say something like this should work: odata/Categories?$expand=Products&$select=Name,Products/Name. Could this be a difference between Odata v3 and v4? – Patrick Borkowicz May 25 '16 at 18:23
  • That is indeed the difference between v3 and v4, just like rama found out at http://stackoverflow.com/a/26746527/462781 And use & instead of the ; indeed. – Pete Jun 23 '16 at 23:20
  • @Jerther Have a look this source https://msdn.microsoft.com/en-us/library/gg334767.aspx for reference. – Ishwor Khanal Mar 01 '18 at 22:55
  • Works perfectly Thank you! – Roberto Bonini Mar 29 '18 at 16:53
  • This doesn't work for `https://graph.microsoft.com/v1.0/users?$expand=manager($select=id,displayName)` for some reason. I get the error "Invalid $select properties." Does it only work for some navigational properties and not others? Try that request here: https://developer.microsoft.com/en-us/graph/graph-explorer – VinnyQ77 Apr 07 '21 at 18:43
2

in the select, you can use the entity/attribute to select specific attributes of expanded entities:

$select=productItemChoices/columnyouwant

I'm not sure about the relationships of the entities in your query. When expanding deeply, I have used the following syntax (without the parentheses) - In the following, I'm assuming that the Products relate to productItemChoices and productItemChoices have item 

$expand=productItemChoices, productItemChoices/item

Then, to select

$select=productItemChoices/productItemChoicesGuid, productItemChoices/item/name

Obviously, if the relationships are different, you'll need to change it some

snow_FFFFFF
  • 3,235
  • 17
  • 29
  • 1
    i tried this odata/Products(08f80b45-68a9-4a9f-a516-556e69e6bd58)?$expand=productItemChoices($expand=item)&$select=productItemChoices/productItemChoicesGuid and it gives me an error saying { "error": { "code": "", "message": "The query specified in the URI is not valid. Found a path with multiple navigation properties in a select clause. Please reword your query such that each level of select or expand only contains either TypeSegments or Properties." } } – pranag Feb 04 '15 at 17:13
  • Edited the answer with some more details – snow_FFFFFF Feb 04 '15 at 17:24
  • 1
    I'm talking of odata v4 here... the query that you have given might work on v3 – pranag Feb 04 '15 at 19:38
  • I'm using v4 as well. My v4 services have been ASP.NET Web API 2.2 OData. Do you know what technology is behind your endpoint? Also, just to be clear, what I'm proposing up there is a single $expand and and single $select in the querystring - I haven't seen the nested parentheses used for $expand/$select before. – snow_FFFFFF Feb 04 '15 at 19:42
  • This worked for me using the SharePoint Online OData service. – Gabriel Smoljar Oct 01 '15 at 06:10