Advanced Topics > Data API > Advanced Filtering

The Data API supports advanced filtering capabilities that allow you to apply custom filter conditions when retrieving table data. Filters can be used to narrow down result sets based on specific column values and conditions.

Overview

Advanced filtering provides the following capabilities:

•Column-based filtering: Filter rows based on specific column values using various comparison operators.

•Complex conditions: Combine multiple filter conditions using logical operators (AND, OR).

•NULL value handling: Query for NULL and non-NULL values in columns.

•API key-level restrictions: Administrators can configure row-level filters at the API key level to restrict data access.

Filter Syntax

Filters are specified using the filter query parameter in JSON array format. The basic structure of a filter condition is:

["ColumnName", "Operator", "Value"]

Supported Operators

The following comparison operators are supported:

Operator	Description
=	Equal to. Use null as the value to check for NULL values.
<> or !=	Not equal to. Use null as the value to check for non-NULL values.
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to
contains	String contains (case-sensitive partial match)
startswith	String starts with (case-sensitive prefix match)

Logical Operators

Multiple filter conditions can be combined using logical operators:

•and - Both conditions must be true

•or - At least one condition must be true

The syntax for combining conditions is:

[["Condition1"], "LogicalOperator", ["Condition2"]]

Usage Examples

Example 1: Filter for NULL Values

Retrieve rows where the EndOfLife column is NULL:

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=["EndOfLife", "=", null]

Example 2: Filter for Non-NULL Values

Retrieve rows where the EndOfLife column is not NULL:

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=["EndOfLife", "<>", null]

Alternatively, you can use != operator:

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=["EndOfLife", "!=", null]

Example 3: Exact Value Match

Retrieve rows where Status equals "Active":

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=["Status", "=", "Active"]

Example 4: Numeric Comparison

Retrieve rows where Price is greater than 100:

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=["Price", ">", 100]

Example 5: String Contains

Retrieve rows where ProductName contains "Windows":

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=["ProductName", "contains", "Windows"]

Example 6: Combined Conditions with AND

Retrieve rows where Status is "Active" AND Price is greater than 100:

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=[["Status", "=", "Active"], "and", ["Price", ">", 100]]

Example 7: Combined Conditions with OR

Retrieve rows where Status is "Active" OR "Pending":

http://[host]:[port]/v1/resultDatabase/<table-name>/paged?filter=[["Status", "=", "Active"], "or", ["Status", "=", "Pending"]]

API Key-Level Filtering

Administrators can configure row-level filters directly on API keys to restrict access to specific rows in result tables. When an API key has row-level filters configured, these filters are automatically combined with any request-level filters using the AND operator.

This allows for centralized access control where:

•API consumers can add additional filters to their requests

•API key-level filters ensure baseline security restrictions are always applied

•Combined filters provide fine-grained access control without requiring API consumers to be aware of security restrictions

See Authentication and Authorization for more information on configuring API key permissions.

Applicable Endpoints

Advanced filtering is supported on the following API endpoints:

•Getting Table Data (Paged Query)

•Getting Table Data (All Rows)

•Getting Deleted Rows (Paged Query)

•Getting Deleted Rows (Full Table)

Best Practices

•Use correct NULL syntax: Always use ["ColumnName", "=", null] for NULL checks and ["ColumnName", "<>", null] or ["ColumnName", "!=", null] for non-NULL checks.

•URL encode filter parameters: When using filters in URLs, ensure proper URL encoding of special characters and JSON structures.

•Filter at the source: Apply filters at the API level rather than retrieving all data and filtering client-side to reduce data transfer and improve performance.

•Combine with pagination: Use filtering together with pagination to efficiently process large filtered datasets.

•Test complex filters: Verify complex filter combinations return expected results before using in production integrations.

Limitations

•String comparisons using contains and startswith are case-sensitive.

•Filter syntax must be valid JSON format.

•Column names must match exactly as they appear in the result table schema.

•Complex nested conditions may impact query performance on very large datasets.

Advanced Filtering