filter returns: property is not a single value

Question

I would like to present the following problem I am having with the OData filter option.

Any ideas on how to solve this problem would be highly appreciated.

My goal is to parse JSON format and return only the value

"WorkstationId": "8E5B3291-E092-4091-8C9A-58B7C90E907C",  

I have created the following request but it generates the following error message; https://doesnotexist/MyApi/api/v1.0/locations?$expand=Workstations,AccessDomains,DefaultProfiles&$filter=Workstations/WorkstationName eq 'LP08'

  "error": {     "code": "",     "message": "The query specified in the URI is not valid. The parent value for a property access of a property 'WorkstationName' is not a single value. Property access can only be applied to a single value.",

Error is caused by my filter option:

filter=Workstations/WorkstationName eq 'LP08'  

My JSON file;

  "@odata.context": "https://doesnotexist/MyApi/api/v1.0/$metadata#locations",
  "value": [
    {
      "LocationIdInternal": 1,
      "LocationId": "B9507A00-9057-4CCC-A66B-9AAAB1B6CA5B",
      "DisplayName": "DEFAULT_LOCATION",
      "IsActive": true,
      "Workstations": [
        {
          "WorkstationIdInternal": 1,
          "WorkstationId": "E4FC58FB-7989-4C87-9216-0A3B9F52860E",
          "WorkstationName": "EX08",
          "WorkstationType": "Default"
        },
        {
          "WorkstationIdInternal": 2,
          "WorkstationId": "8E6DB912-F74F-444C-98D6-179747BBDE1A",
          "WorkstationName": "LP08",
          "WorkstationType": "Default"
        },
        {
          "WorkstationIdInternal": 3,
          "WorkstationId": "8E5B3291-E092-4091-8C9A-58B7C90E907C",
          "WorkstationName": "OVER77",
          "WorkstationType": "Default"
        }
      ]
    }
  ]
}

Answer 1

expanding on the solution offered by @Phyxius, because you are querying the Locations controller and each location can have many Workstations to filter the result set to only the locations that have a workstation named "LP08" and to old return those workstations that also match the filter we need to apply the filter in two places:

1. To only return locations that have a workstation that matches the filter, we need to use the Any operator:

   https://doesnotexist/MyApi/api/v1.0/locations?$expand=Workstations,AccessDomains,DefaultProfiles&$filter=Workstations/Any(w:w/WorkstationName eq 'LP08')
   

2. Now to restrict the expanded Workstations so that only the matching workstation is returned, we also have to define the $filter inside the expansion.

   https://doesnotexist/MyApi/api/v1.0/locations?$expand=Workstations($filter=WorkstationName eq 'LP08'),AccessDomains,DefaultProfiles&$filter=Workstations/Any(w:w/WorkstationName eq 'LP08')
   

The reason for the two filters is that the root level $filter option resticts the number of results, but it does not affect the projection of those results. This is much the same way that a WHERE clause in SQL can reference fields that are not included in the result set at all.

Only the $select and $expand query options affect the projection of the resulting records, and we can use $select;$filter;$orderby and even nested $expand operators inside the $expand query option.

---

My goal is to parse JSON format and return only the value

"WorkstationId": "8E5B3291-E092-4091-8C9A-58B7C90E907C",  

If you only want to return the WorkstationId value then it might be simpler to remove the other $expand parameters and to add a $select into the expansion:

https://doesnotexist/MyApi/api/v1.0/locations?$expand=Workstations($filter=WorkstationName eq 'LP08';$select=WorkstationName)&$filter=Workstations/Any(w:w/WorkstationName eq 'LP08')

However this will still return the results in a nested array, even though it will only have a single value within it.

{
  "@odata.context": "https://doesnotexist/MyApi/api/v1.0/$metadata#locations",
  "value": [
    {
      "LocationIdInternal": 1,
      "LocationId": "B9507A00-9057-4CCC-A66B-9AAAB1B6CA5B",
      "DisplayName": "DEFAULT_LOCATION",
      "IsActive": true,
      "Workstations": [
        {
          "WorkstationId": "8E6DB912-F74F-444C-98D6-179747BBDE1A"
        }
      ]
    }
  ]
}

If you only wanted to return the Workstation result without it being wrapped in a location, then you should be able to query the Workstations controller, instead of the Locations one:
This only works is your model supports this navigation but can be helpful to remember that the "root" item type must always be the type specified by the controller or function.

https://doesnotexist/MyApi/api/v1.0/workstations?$expand=Locations&$filter=WorkstationName eq 'LP08'&$select=WorkstationId

{
  "@odata.context": "https://doesnotexist/MyApi/api/v1.0/$metadata#workstations",
  "value": [
    {
      "WorkstationId": "8E6DB912-F74F-444C-98D6-179747BBDE1A",
      "Locations": [
        {
          "LocationIdInternal": 1,
          "LocationId": "B9507A00-9057-4CCC-A66B-9AAAB1B6CA5B",
          "DisplayName": "DEFAULT_LOCATION",
          "IsActive": true
        }
      ]
    }
  ]
}

Omit the expand clause altogether if you don't need the location information.

Answer 2

just put the filter within $expand:

https://doesnotexist/MyApi/api/v1.0/locations?$expand=Workstations($filter= WorkstationName eq 'LP08'),AccessDomains,DefaultProfiles

This will only expand workstations with that name