Business Central API - Filter Child Array Values Using $filter Query Option

 by Steve Endow

I had a difficult time finding an example of this type of API filtering using the OData $filter query option, so I wanted to share an example that I needed for a project.

For context, this is a very simple example of a Business Central API "child array" that I wanted to filter.  Technically, I think that OData calls this an "expanded navigation property".

Customers with Expanded contactsInformation Navigation Property

First, if you are not familiar with the OData $metadata URL and the $expand query parameter, please see my blog post here.

Second, I'd like to thank AJ Kauffman for helping me understand how to use this type of filter.  If you want to learn more about how to work with Business Central APIs, I highly recommend AJ's class.

https://www.kauffmann.nl/bc-apis-online-course/

One slide that I constantly refer to from AJ's API class is a list of 7 common OData query parameters.

Know the 7 Basic OData Query Parameters

The OData v4 specification refers to these as "Query Options".

So let's explore a specific use case for the $filter query option.  This sample JSON shows a customer record and the expanded contactsInformation navigation property.

Here is the URL:

http://bcdev-default:7048/BC/api/v2.0/companies(684c3004-464e-ec11-bb7e-000d3a39265e)/

customers(461d8d2a-464e-ec11-bb7e-000d3a39265e)?$expand=contactsInformation

And here is the response, showing multiple contacts for the customer:

{

    "@odata.context": "http://bcdev-default:7048/BC/api/v2.0/$metadata#companies(684c3004-464e-ec11-bb7e-000d3a39265e)/customers/$entity",

    "@odata.etag": "W/\"JzQ0O3VXUnY4ZHNyb3dyMGVja2owMTh4QmJabEJqVmVndHdmSHV2ZE5PdDVMSTQ9MTswMDsn\"",

    "id": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

    "number": "10000",

    "displayName": "Adatum Corporation",

    "type": "Company",

    "addressLine1": "192 Market Square",

    "addressLine2": "",

    "city": "Atlanta",

    "state": "GA",

    "country": "US",

    "postalCode": "31772",

    "phoneNumber": "",

    "email": "robert.townes@contoso.com",

    "website": "",

    "salespersonCode": "JO",

    "balanceDue": 0,

    "creditLimit": 0,

    "taxLiable": true,

    "taxAreaId": "6701222f-464e-ec11-bb7e-000d3a39265e",

    "taxAreaDisplayName": "ATLANTA, GA",

    "taxRegistrationNumber": "",

    "currencyId": "00000000-0000-0000-0000-000000000000",

    "currencyCode": "USD",

    "paymentTermsId": "551c8d2a-464e-ec11-bb7e-000d3a39265e",

    "shipmentMethodId": "00000000-0000-0000-0000-000000000000",

    "paymentMethodId": "0e01222f-464e-ec11-bb7e-000d3a39265e",

    "blocked": "_x0020_",

    "lastModifiedDateTime": "2021-11-25T23:20:30.547Z",

    "contactsInformation": [

        {

            "@odata.etag": "W/\"JzQ0O0hYRTV0WnVQc0FIWUN5d0ZWY2QwOS94dVJvelBqVUdPTEVCUlZSbDNvQU09MTswMDsn\"",

            "contactId": "ee01222f-464e-ec11-bb7e-000d3a39265e",

            "contactNumber": "CT000001",

            "contactName": "Adatum Corporation",

            "contactType": "Company",

            "relatedId": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

            "relatedType": "Customer"

        },

        {

            "@odata.etag": "W/\"JzQ0O01JdGtxcUxla0d6MDJHWnl0VklOdnNlUy9uMjZGWVdDeFJ2VisySGxINTg9MTswMDsn\"",

            "contactId": "ef01222f-464e-ec11-bb7e-000d3a39265e",

            "contactNumber": "CT000002",

            "contactName": "Robert Townes",

            "contactType": "Person",

            "relatedId": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

            "relatedType": "Customer"

        }

    ]

}

So now that I have the contacts for the customer, what if I want to filter those contacts?  Perhaps I only want to see records with contactType = "Person".  How do you use $filter to achieve that?  

If you try this:

    /customers({{customerid}})?$expand=contactsInformation&$filter=contactType eq 'Person'

You will get this error:

        "code": "BadRequest",

        "message": "Could not find a property named 'contactType' on type 'Microsoft.NAV.customer'."  

Because the $filter is applied on the "parent" customer record, there is no contactType field available.

So what if we try this:

    /customers({{customerid}})?$expand=contactsInformation&$filter=contactsInformation/contactType eq 'Person'

Well, that won't work either.  You'll get this error:

        "code": "BadRequest",

        "message": "The parent value for a property access of a property 'contactType' is not a single value. Property access can only be applied to a single value."

So, how do we filter the records in a "child" array?  This is the way:

    /customers({{customerid}})?$expand=contactsInformation($filter=contactType eq 'Person')

That gives you this response, with only the Person contact type in the contactsInformation array:

    "lastModifiedDateTime": "2021-11-25T23:20:30.547Z",

    "contactsInformation": [

        {

            "@odata.etag": "W/\"JzQ0O01JdGtxcUxla0d6MDJHWnl0VklOdnNlUy9uMjZGWVdDeFJ2VisySGxINTg9MTswMDsn\"",

            "contactId": "ef01222f-464e-ec11-bb7e-000d3a39265e",

            "contactNumber": "CT000002",

            "contactName": "Robert Townes",

            "contactType": "Person",

            "relatedId": "461d8d2a-464e-ec11-bb7e-000d3a39265e",

            "relatedType": "Customer"

        }

    ]

After AJ showed me how to do this, I did find an example in the OData documentation, but you really have to know what you are looking for to see it.

http://docs.oasis-open.org/odata/odata/v4.0/os/part2-url-conventions/odata-v4.0-os-part2-url-conventions.html#_Toc372793860

Like finding a tiny footnote in a giant book

I hope that made sense and helps someone else out there looking to use this type of array filtering!

Steve Endow is a Microsoft MVP in Los Angeles.  He works with Dynamics 365 Business Central.

You can also find him on Twitter and YouTube