Filtering data in REST ($filter)

Filtering narrows large datasets to only the records you need. In REST, Data API builder (DAB) supports an OData-inspired $filter query parameter. Each filter compiles to parameterized SQL for safety and consistency.

Quick glance

eq

Equal to. Returns records where a field’s value exactly matches the provided literal or null.

In this example, we're getting books where the title equals 'Dune', the available flag is true, the price is 20, the published date is January 1, 2024, and the rating is null.

GET /api/books?$filter=
  title eq 'Dune' and
  available eq true and
  price eq 20 and
  published_on eq 2024-01-01T00:00:00Z and
  rating eq null

Conceptual SQL

SELECT * FROM Books
WHERE title = 'Dune'
  AND available = 1
  AND price = 20
  AND published_on = '2024-01-01T00:00:00Z'
  AND rating IS NULL;

ne

Not equal to. Returns records where a field’s value doesn’t match the given literal or isn’t null.

In this example, we're getting books where the title isn't 'Foundation', the available flag isn't false, the price isn't zero, the published date isn't December 31, 2023, and the rating isn't null.

GET /api/books?$filter=
  title ne 'Foundation' and
  available ne false and
  price ne 0 and
  published_on ne 2023-12-31T00:00:00Z and
  rating ne null

Conceptual SQL

SELECT * FROM Books
WHERE title <> 'Foundation'
  AND available <> 0
  AND price <> 0
  AND published_on <> '2023-12-31T00:00:00Z'
  AND rating IS NOT NULL;

gt

Greater than. Returns records where a field’s value is strictly higher than the given literal.

In this example, we're getting books whose title sorts alphabetically after 'A', the available flag is true, the price is greater than 10, and the published date is after January 1, 2020.

GET /api/books?$filter=
  title gt 'A' and
  available gt false and
  price gt 10 and
  published_on gt 2020-01-01T00:00:00Z

Conceptual SQL

SELECT * FROM Books
WHERE title > 'A'
  AND available > 0
  AND price > 10
  AND published_on > '2020-01-01T00:00:00Z';

ge

Greater than or equal. Returns records where a field’s value is higher than or equal to the given literal.

In this example, we're getting books whose title is 'A' or later, the available flag is true, the price is at least 10, and the published date is on or after January 1, 2020.

GET /api/books?$filter=
  title ge 'A' and
  available ge false and
  price ge 10 and
  published_on ge 2020-01-01T00:00:00Z

Conceptual SQL

SELECT * FROM Books
WHERE title >= 'A'
  AND available >= 0
  AND price >= 10
  AND published_on >= '2020-01-01T00:00:00Z';

lt

Less than. Returns records where a field’s value is strictly lower than the given literal.

In this example, we're getting books whose title sorts before 'Z', the available flag is false, the price is less than 50, and the published date is before January 1, 2030.

GET /api/books?$filter=
  title lt 'Z' and
  available lt true and
  price lt 50 and
  published_on lt 2030-01-01T00:00:00Z

Conceptual SQL

SELECT * FROM Books
WHERE title < 'Z'
  AND available < 1
  AND price < 50
  AND published_on < '2030-01-01T00:00:00Z';

le

Less than or equal. Returns records where a field’s value is lower than or equal to the given literal.

In this example, we're getting books whose title sorts before or equal to 'Z', the available flag is true, the price is 100 or less, and the published date is on or before January 1, 2030.

GET /api/books?$filter=
  title le 'Z' and
  available le true and
  price le 100 and
  published_on le 2030-01-01T00:00:00Z

Conceptual SQL

SELECT * FROM Books
WHERE title <= 'Z'
  AND available <= 1
  AND price <= 100
  AND published_on <= '2030-01-01T00:00:00Z';

and

Logical AND. Combines multiple conditions that must all be true for a record to match.

In this example, we're getting books where the title is 'Dune', the available flag is true, the price is less than 50, the published date is after January 1, 2020, and the rating is null.

GET /api/books?$filter=
  title eq 'Dune' and
  available eq true and
  price lt 50 and
  published_on ge 2020-01-01T00:00:00Z and
  rating eq null

Conceptual SQL

SELECT * FROM Books
WHERE title = 'Dune'
  AND available = 1
  AND price < 50
  AND published_on >= '2020-01-01T00:00:00Z'
  AND rating IS NULL;

or

Logical OR. Combines conditions where at least one must be true for a record to match.

In this example, we're getting books where the title is 'Dune', or the available flag is true, or the price is greater than 20, or the published date is before January 1, 2025, or the rating is null.

GET /api/books?$filter=
  title eq 'Dune' or
  available eq true or
  price gt 20 or
  published_on lt 2025-01-01T00:00:00Z or
  rating eq null

Conceptual SQL

SELECT * FROM Books
WHERE title = 'Dune'
  OR available = 1
  OR price > 20
  OR published_on < '2025-01-01T00:00:00Z'
  OR rating IS NULL;

not

Logical NOT. Negates a condition so records are returned only if the condition is false.

In this example, we're getting all books that don’t have the title 'Romance', aren’t unavailable, don’t cost less than $10, weren’t published before January 1, 2020, and don’t have a null rating.

GET /api/books?$filter=
  not (
    title eq 'Romance' and
    available eq false and
    price lt 10 and
    published_on lt 2020-01-01T00:00:00Z and
    rating eq null
  )

Conceptual SQL

SELECT * FROM Books
WHERE NOT (
  title = 'Romance'
  AND available = 0
  AND price < 10
  AND published_on < '2020-01-01T00:00:00Z'
  AND rating IS NULL
);

( ) grouping

Groups subexpressions so you can control evaluation order in complex filters.

In this example, we're getting books where the title is either 'Fiction' or 'SciFi', and the book is either available or priced below $25, and the published date is after January 1, 2020, and the rating is null.

GET /api/books?$filter=
  (title eq 'Fiction' or title eq 'SciFi') and
  (available eq true or price lt 25) and
  published_on ge 2020-01-01T00:00:00Z and
  rating eq null

Conceptual SQL

SELECT * FROM Books
WHERE (title = 'Fiction' OR title = 'SciFi')
  AND (available = 1 OR price < 25)
  AND published_on >= '2020-01-01T00:00:00Z'
  AND rating IS NULL;

Example configuration

{
  "runtime": {
    "pagination": {
      "default-page-size": 100,
      "max-page-size": 100000
    }
  },
  "entities": {
    "Book": {
      "source": {
        "type": "table",
        "object": "dbo.books"
      },
      "mappings": {
        "sku_title": "title",
        "sku_price": "price"
      },
      "relationships": {
        "book_category": {
          "cardinality": "one",
          "target.entity": "Category",
          "source.fields": [ "category_id" ],
          "target.fields": [ "id" ]
        }
      }
    },
    "Category": {
      "source": {
        "type": "table",
        "object": "dbo.categories"
      },
      "relationships": {
        "category_books": {
          "cardinality": "many",
          "target.entity": "Book",
          "source.fields": [ "id" ],
          "target.fields": [ "category_id" ]
        }
      }
    }
  }
}

See also