Filtering Data

Learn how to effectively filter data using OData query syntax.

Basic Filtering

The $filter query option allows you to retrieve only records that match specific criteria.

Syntax

GET /{EntitySet}?$filter={expression}

Simple Equality

Filter for an exact match:

# Patients with last name 'Smith'
GET /Patients?$filter=last_name eq 'Smith'

# Appointments with status 'confirmed'
GET /Appointments?$filter=status eq 'confirmed'

Numeric Comparisons

Use comparison operators for numbers and dates:

# Patients older than 18
GET /Patients?$filter=age gt 18

# Appointments after a specific date
GET /Appointments?$filter=appointment_date ge '2024-01-01'

# Patients between 18 and 65
GET /Patients?$filter=age ge 18 and age le 65

Logical Operators

AND

Combine multiple conditions that must all be true:

# Confirmed appointments for a specific patient
GET /Appointments?$filter=patient_id eq 12345 and status eq 'confirmed'

# Adult patients named John Smith
GET /Patients?$filter=first_name eq 'John' and last_name eq 'Smith' and age gt 18

OR

Match records that satisfy any condition:

# Appointments that are confirmed or pending
GET /Appointments?$filter=status eq 'confirmed' or status eq 'pending'

# Patients named John or Jane
GET /Patients?$filter=first_name eq 'John' or first_name eq 'Jane'

NOT

Negate a condition:

# Appointments that are not cancelled
GET /Appointments?$filter=not (status eq 'cancelled')

# Equivalent to:
GET /Appointments?$filter=status ne 'cancelled'

String Functions

contains()

Check if a string contains a substring:

# Patients with 'smith' anywhere in last name
GET /Patients?$filter=contains(last_name,'smith')

# Case-sensitive in some implementations
GET /Patients?$filter=contains(last_name,'Smith')

startswith()

Match strings that start with a specific value:

# Patients with last name starting with 'Sm'
GET /Patients?$filter=startswith(last_name,'Sm')

# Appointments in morning time slots (9:00 AM)
GET /Appointments?$filter=startswith(appointment_time,'09:')

endswith()

Match strings that end with a specific value:

# Patients with last name ending in 'son'
GET /Patients?$filter=endswith(last_name,'son')

# Emails ending with specific domain
GET /Patients?$filter=endswith(email,'@example.com')

Date and Time Filtering

Date Ranges

Filter by date ranges using comparison operators:

# Appointments in January 2024
GET /Appointments?$filter=appointment_date ge '2024-01-01' and appointment_date le '2024-01-31'

# Future appointments
GET /Appointments?$filter=appointment_date gt '2024-02-17'

# Past appointments
GET /Appointments?$filter=appointment_date lt '2024-02-17'

Date Functions

# Appointments on a specific date
GET /Appointments?$filter=appointment_date eq '2024-02-20'

# Patients born before a certain date
GET /Patients?$filter=date_of_birth lt '2000-01-01'

Use navigation properties in filter expressions:

# Patients with at least one appointment
GET /Patients?$filter=Appointments/any()

# Patients with confirmed appointments
GET /Patients?$filter=Appointments/any(a: a/status eq 'confirmed')

# Patients where all appointments are confirmed
GET /Patients?$filter=Appointments/all(a: a/status eq 'confirmed')

# Appointments with a specific physician
GET /Appointments?$filter=Physician/physician_id eq 67890

Complex Filters

Combine multiple techniques for sophisticated queries:

# Adult patients named Smith with confirmed appointments in February
GET /Patients
  ?$filter=last_name eq 'Smith'
    and age gt 18
    and Appointments/any(a:
      a/status eq 'confirmed'
      and a/appointment_date ge '2024-02-01'
      and a/appointment_date le '2024-02-29'
    )

Null Values

Check for null or non-null values:

# Patients with no email address
GET /Patients?$filter=email eq null

# Patients with an email address
GET /Patients?$filter=email ne null

Common Patterns

Search by Name

# Case-insensitive search (if supported)
GET /Patients?$filter=contains(tolower(last_name),'smith')

# Search by first or last name
GET /Patients
  ?$filter=contains(first_name,'John') or contains(last_name,'John')

Status Filtering

# Active appointments
GET /Appointments
  ?$filter=status ne 'cancelled' and appointment_date ge '2024-02-17'

# Completed appointments
GET /Appointments
  ?$filter=status eq 'completed' and appointment_date lt '2024-02-17'

Date-Based Queries

# This month's appointments
GET /Appointments
  ?$filter=appointment_date ge '2024-02-01' and appointment_date lt '2024-03-01'

# Today's appointments
GET /Appointments?$filter=appointment_date eq '2024-02-17'

# Upcoming appointments
GET /Appointments
  ?$filter=appointment_date ge '2024-02-17'
    and status ne 'cancelled'
  &$orderby=appointment_date

Performance Tips

Optimize Your Filters

Index-friendly filters: Filter on indexed columns (IDs, status, dates)
Specific before general: Put more restrictive filters first
Avoid complex string operations: Use exact matches when possible
Limit results: Combine with $top to reduce response size

Avoid These Patterns

Not using filters: Retrieving all data and filtering client-side
Too complex: Filters that are difficult to optimize
Case-insensitive text search: Can be slow on large datasets
Too many OR conditions: Consider breaking into multiple requests

Examples by Use Case

Patient Lookup

# By full name
GET /Patients
  ?$filter=first_name eq 'John' and last_name eq 'Smith'

# By partial name
GET /Patients?$filter=contains(last_name,'Smi')

# By date of birth
GET /Patients?$filter=date_of_birth eq '1980-05-15'

Appointment Management

# Today's confirmed appointments
GET /Appointments
  ?$filter=appointment_date eq '2024-02-17' and status eq 'confirmed'
  &$expand=Patient,Physician
  &$orderby=appointment_time

# Pending appointments needing confirmation
GET /Appointments
  ?$filter=status eq 'pending' and appointment_date ge '2024-02-17'
  &$orderby=appointment_date

Reporting Queries

# Monthly appointment count by status
GET /Appointments
  ?$filter=appointment_date ge '2024-02-01' and appointment_date lt '2024-03-01'
  &$count=true

# New patients this year
GET /Patients
  ?$filter=created_date ge '2024-01-01'
  &$count=true

Troubleshooting

Common Errors

Error: "Property 'xyz' does not exist"

Solution: Check property names match the schema in /$metadata

Error: "Invalid filter expression"

Solution: Verify syntax, especially: - String values use single quotes: 'value' - Property names are case-sensitive - Operators are lowercase: eq, not EQ

Error: "The query is too complex"

Solution: Simplify the filter or split into multiple requests