Odata Lambda Operator Nested Structure

Question

I am trying to query an API that uses Odata. I have to filter based on the following structure:

{
"@odata.context": "http://services.odata.org/V4/TripPinService/$metadata#People",
"@odata.nextLink": "https://services.odata.org/V4/TripPinService/People?%24expand=Friends&%24skiptoken=8",
"value": [
    {
        "@odata.id": "http://services.odata.org/V4/TripPinService/People('russellwhyte')",
        "@odata.etag": "W/\"08DA99A81C62685E\"",
        "@odata.editLink": "http://services.odata.org/V4/TripPinService/People('russellwhyte')",
        "UserName": "russellwhyte",
        "FirstName": "Russell",
        "LastName": "Whyte",
        "Emails": [
            "Russell@example.com",
            "Russell@contoso.com"
        ],
        "AddressInfo": [
            {
                "Address": "187 Suffolk Ln.",
                "City": {
                    "CountryRegion": "United States",
                    "Name": "Boise",
                    "Region": "ID"
                }
            }
        ],
        "Gender": "Male",
        "Concurrency": 637991244536113246,
        "Friends": [
            {
                "@odata.id": "http://services.odata.org/V4/TripPinService/People('scottketchum')",
                "@odata.etag": "W/\"08DA99A81C62685E\"",
                "@odata.editLink": "http://services.odata.org/V4/TripPinService/People('scottketchum')",
                "UserName": "scottketchum",
                "FirstName": "Scott",
                "LastName": "Ketchum",
                "Emails": [
                    "Scott@example.com"
                ],
                "AddressInfo": [
                    {
                        "Address": "2817 Milton Dr.",
                        "City": {
                            "CountryRegion": "United States",
                            "Name": "Albuquerque",
                            "Region": "NM"
                        }
                    }
                ],
                "Gender": "Male",
                "Concurrency": 637991244536113246
            },
            {
                "@odata.id": "http://services.odata.org/V4/TripPinService/People('ronaldmundy')",
                "@odata.etag": "W/\"08DA99A81C62685E\"",
                "@odata.editLink": "http://services.odata.org/V4/TripPinService/People('ronaldmundy')",
                "UserName": "ronaldmundy",
                "FirstName": "Ronald",
                "LastName": "Mundy",
                "Emails": [
                    "Ronald@example.com",
                    "Ronald@contoso.com"
                ],
                "AddressInfo": [],
                "Gender": "Male",
                "Concurrency": 637991244536113246
            },
            {
                "@odata.id": "http://services.odata.org/V4/TripPinService/People('javieralfred')",
                "@odata.etag": "W/\"08DA99A81C62685E\"",
                "@odata.editLink": "http://services.odata.org/V4/TripPinService/People('javieralfred')",
                "UserName": "javieralfred",
                "FirstName": "Javier",
                "LastName": "Alfred",
                "Emails": [
                    "Javier@example.com",
                    "Javier@contoso.com"
                ],
                "AddressInfo": [
                    {
                        "Address": "89 Jefferson Way Suite 2",
                        "City": {
                            "CountryRegion": "United States",
                            "Name": "Portland",
                            "Region": "WA"
                        }
                    }
                ],
                "Gender": "Male",
                "Concurrency": 637991244536113246
            }
        ]
    }

Of course this is an entity provided in the Odata library example project, and can be consumed using http://services.odata.org/V4/TripPinService/People?$expand=Friends$

I need to get people that have a friend that have al least an email equal to 'Scott@example.com'.

So far I tried variations of /People?$filter=Friends/any(f: f/Emails/any(e: eq 'Scott@example.com')) but getting error

"message": "Term 'Friends$filter=Friends/any(f: f/Emails/any(e: eq 'Scott@example.com'))' is not valid in a $select or $expand expression."

How can I do this correctly?

user2250152 · Answer

You can use $filter inside $expand.

Either filter Emails where value inside Emails collection is equal to scott@example.com or filter Emails that contains scott@example.com.

http://services.odata.org/V4/TripPinService/People?$expand=Friends($filter=Emails/any(r:r eq 'scott@example.com'))

http://services.odata.org/V4/TripPinService/People?$expand=Friends($filter=Emails/any(r:contains(r,'scott@example.com')))

These queries have correct syntax but for some reason both queries return all data for http://services.odata.org/V4/TripPinService/People endpoint.

I'm afraid that $filter inside $expand is not implemented or supported for that endpoint.

Odata Lambda Operator Nested Structure

1 Answers