OData $filter Queries Explained

Learn How to Use OData $filter Queries

OData is a standard data access protocol that allows clients to query and filter data from APIs using a standardized syntax, such as the $filter query option for retrieving specific subsets of data based on defined criteria. In this blog post, we explain how to use OData $filter queries.

Here’s a scenario: you have been asked to build a data view on your company’s ERP system, such as Microsoft Dynamics or SAP. The ERP system is your data source. The data view is the final output that will be presented to management.

But how do you get the data from inside your ERP into your data view? That’s where OData, or the Open Data Protocol comes in. OData facilitates seamless communication between a data consumer (the data view) and a data provider (the ERP system) by standardizing how data is queried, filtered, and manipulated over the web.

OData $filter: A Simple Scenario

Let’s imagine we have an OData service, such as Dynamics or an SAP ERP, that exposes an Employees entity set.

Each employee has the following properties:

• ID (integer)
• Name (string)
• Age (integer)
• Department (string)
• Salary (decimal)
• Location/City (string)

1. Basic Filtering: Filtering by a Single Property

Let’s start with basic filtering. We want the OData endpoint to return all employees who are older than 30. What is the OData query that we need to write?

Here is what our query should look like:

GET serviceRoot/Employees?$filter=Age gt 30

This simple filter uses OData’s range operator to test whether a field is greater than a constant value.

Presuming that our ERP only has two employees who are older than 30, here is a sample JSON response:

{
  "value": [
    {
      "ID": 1,
      "Name": "Alice Johnson",
      "Age": 35,
      "Department": "Engineering",
      "Salary": 80000,
      "Location": {
        "City": "New York"
      }
    },
    {
      "ID": 2,
      "Name": "Bob Smith",
      "Age": 40,
      "Department": "HR",
      "Salary": 60000,
      "Location": {
        "City": "San Francisco"
      }
    }
  ]
}

Other comparison operators in OData that work with the filtering function are:

OData Equality Operators

eq: Test whether a field is equal to a constant value
ne: Test whether a field is not equal to a constant value

OData Range Operators

• gt: Test whether a field is greater than a constant value
• lt: Test whether a field is less than a constant value
• ge: Test whether a field is greater than or equal to a constant value
• le: Test whether a field is less than or equal to a constant value

2. Filtering by Multiple Properties: Combining Conditions with and

Next up, let’s add another condition to our OData filter. This time management would like to know which employees are older than 30 and work in the “Engineering” department.

Here is what our OData query should look like:

GET serviceRoot/Employees?$filter=Age gt 30 and Department eq 'Engineering'

This OData query filters employees based on two conditions: their Age must be greater than 30, and their Department must be equal to (eq) “Engineering”.

In our sample JSON response we now only see one employee, Alice, who matches our criteria.

{
  "value": [
    {
      "ID": 1,
      "Name": "Alice Johnson",
      "Age": 35,
      "Department": "Engineering",
      "Salary": 80000,
      "Location": {
        "City": "New York"
      }
    }
  ]
}

OData’s supports three logical operators: are and, or and not.

and evaluates to true if both its left and right sub-expressions evaluate to true or if both conditions are met. or evaluates to true if either one of its left or right sub-expressions evaluates to true. Last, not is a unary operator that evaluates to true if its sub-expression evaluates to false, and vice-versa.

We will take a closer look at the or operator next.

3. Using or Operator: Filtering with Alternative Conditions

Management has another job for you. Now they need an overview of all employees who either work in “HR” or have a salary greater than 70,000.

Here is our Odata $filter query that returns the right response:

GET serviceRoot/Employees?$filter=Department eq 'HR' or Salary gt 70000

The query returns employees who meet either of the two conditions: working in “HR” or having a salary greater than 70,000.

The (by now, very familiar), sample JSON response looks like this. Bob works in HR, but has a salary of less than 70,000. Alice, on the other hand, works in Engineering, but her salary is above 70,000. With the or operator only one of our conditions has to evaluate to true.

{
  "value": [
    {
      "ID": 2,
      "Name": "Bob Smith",
      "Age": 40,
      "Department": "HR",
      "Salary": 60000,
      "Location": {
        "City": "San Francisco"
      }
    },
    {
      "ID": 1,
      "Name": "Alice Johnson",
      "Age": 35,
      "Department": "Engineering",
      "Salary": 80000,
      "Location": {
        "City": "New York"
      }
    }
  ]
}

4. Using Functions: Filtering with startswith and contains

Let’s use our OData $filter to get employees whose names start with “A” and live in a city that contains “York”.

GET serviceRoot/Employees?$filter=startswith(Name, 'A') and contains(Location/City, 'York')

This query uses the startswith function to filter employees whose Name starts with “A”. It also uses contains to filter based on whether the City in Location contains the string “York”.

Her is what our sample JSON response returns:

{
  "value": [
    {
      "ID": 1,
      "Name": "Alice Johnson",
      "Age": 35,
      "Department": "Engineering",
      "Salary": 80000,
      "Location": {
        "City": "New York"
      }
    }
  ]
}

5. Complex Filtering: Combining Multiple Conditions and Functions

Reality is rarely as simple as the simple filters described above. So let’s make things a little bit more complex.

Let’s get employees who are older than 30, work in “Engineering”, have a salary greater than 70,000, and live in either “New York” or “San Francisco”.

GET serviceRoot/Employees?$filter=Age gt 30 and Department eq 'Engineering' and Salary gt 70000 and (Location/City eq 'New York' or Location/City eq 'San Francisco')

This query combines multiple conditions with both and and or operators, filtering for employees who meet all the specified criteria.

Here is our sample JSON response. Alice fulfills all four conditions:

{
  "value": [
    {
      "ID": 1,
      "Name": "Alice Johnson",
      "Age": 35,
      "Department": "Engineering",
      "Salary": 80000,
      "Location": {
        "City": "New York"
      }
    }
  ]
}

6. Filtering with Nested Properties: Accessing Nested Structures

Get employees who live in a city that starts with “San” and have a salary less than 70,000.

GET serviceRoot/Employees?$filter=startswith(Location/City, 'San') and Salary lt 70000

This query filters based on the nested Location/City property and also checks the Salary to ensure it is less than (lt) 70,000.

Here is our sample JSON response:

{
  "value": [
    {
      "ID": 3,
      "Name": "Charlie Brown",
      "Age": 28,
      "Department": "Finance",
      "Salary": 65000,
      "Location": {
        "City": "San Francisco"
      }
    }
  ]
}

Summary: OData $filter

OData’s powerful query language makes it a powerful data access technology.

These examples demonstrate how OData’s $filter query option can be used to retrieve data based on specific conditions. By progressively building more complex queries, you can gain finer control over the data you retrieve, making OData a powerful tool for querying APIs!

Connect to OData, Build a Web App
Rapidly build and deploy custom software