Filtering
Many Commerce API endpoints support filtering. The general syntax is described below, but you must go to a specific endpoint page to understand the attributes and operators an endpoint supports.
Filtering
Elastic Path is constantly improving the filtering and search capabilities. The syntax documented below is used in most of the platform however a few APIs still need to be updated. Please see the Legacy Syntax below.
Supported Endpoints
Filtering is currently supported on the following endpoints:
- Get all accounts
- Get all account members
- Get all account memberships
- Get all account memberships on account member
- Get all unassigned account members
- Get all promotions
- Get all price books
- Get all price modifiers
- Get all prices in a price book
- Get all products
- Get all product prices
- Get all locations
- Get all transactions for product
- Get all stock
- Get all Custom APIs
- Get all Custom Fields
- Get all Custom API Entries
- Get all Custom API Role Policies
- Get Usages for Promotion
- Get a Promotion Code Usage
- Get all subscription jobs
- Get all schedules
- Get all subscription products
- Get all subscription invoices
- Get all subscribers
- Get all subscription offerings
- Get all subscriptions
- Get all dunning rules
The filter
query parameter can be supplied to supported endpoints. The value of this query parameter is a series of
one or more operators, along with its operands. For instance filter=eq(email,ron@swanson.com)
has an operator of eq
and operands of email
and ron@swanson.com
. The first operand for all operators is the field on the resource that is being filtered.
Supported Operators
In general, the following operators are supported. However, not every field supports every operator. See the Filtering section for each endpoint to see the operators an endpoint supports.
Operator | Description |
---|---|
contains | Contains. Checks if the field (of type array) in the first operand contains the second operand. If it does, the condition is true. |
eq | Equals. Checks if the field in the first operand is equal to the second operand. If they are, the condition is true. |
like | Like. Checks if the field in the first operand contains the specified string. Wildcards are supported, at either the beginning, end or both. |
ilike | Case-Insensitive Like. Checks if the field in the first operand contains the specified string in a case-insensitive manner. Wildcards are supported, at either the beginning, end or both. |
in | In. Checks if the field in the first operand matches any of the remaining specified values. If they are, the condition is true. Wildcards are not supported. |
gt | Greater than. Checks if the field in the first operand is greater than that of the second. If they are, the condition is true. |
ge | Greater than or equal to. Checks if the field in the first operand is greater than or equal to that of the second. If they are, the condition is true. |
lt | Less than. Checks if the field in the first operand is less than that of the second. If they are, the condition is true. |
le | Less than or equal to. Checks if the field in the first operand is less than or equal to that of the second. If they are, the condition is true. |
is_null | Is null. Checks if the value of the operand is null. If it is, the condition is true. |
Combining Operators
Multiple filters can be combined to create a more complex query, you can use:
- A colon (
:
) to logically AND the results, e.g.,eq(status,complete):eq(payment,paid)
could be used to find orders that have both statuscomplete
and paymentpaid
. - A pipe (
|
) to logically OR the results, e.g.,like(name,*Range*)|like(description,*Range*)
could be used to search for products that haveRange
in either the name or the description. - Parentheses can be used to nest or group filters together e.g.,
(eq(account_id,abc-123)|eq(external_ref,abc-123)):ilike(name,John*)
could be used to search for subscribers that have a name that starts withJohn
and where either the account_id or external_ref matches a value.
Parentheses are optional, but recommended. When specified without parentheses, the :
has higher precedence, meaning that eq(a,b):eq(c,d)|eq(e,f)
is equivalent to (eq(a,b):eq(c,d))|eq(e,f)
.
Specifying Operands
The following characters can be used when constructing an operand:
- Alphanumeric characters:
a-z
,A-Z
,0-9
- Special Characters:
@
,$
,_
,*
,.
,{
,}
,|
,+
,:
,/
,-
- Spaces, however leading spaces are dropped, and it is recommended you use quoted operands described next when you have white space.
Quoted Operands
If you need to search for unsupported operands, or white space is significant (for example, a value starts with a space), you can also quote the operand, as shown below:
filter=eq(name,"Ron Swanson")
filter=gt(updated_at,"2018-04-16T10:11:59.715Z")
You can use '
, or "
as the quoting character, and should escape nested quotes with a \
. For
example: filter=eq(name,'Food \'n Stuff')
URL Encoding Filters
As filters are supplied in the URL, they need to be URL encoded before being sent. It is recommended you always URL encode the entire filter, as some User Agents and libraries may apply heuristics that result in unexpected encodings and incorrect resulting filters.
Supported Date Formats
You can use both Dates and Timestamps in the filters and pass them in as arguments.
Limitations
- You can only filter on base object attributes, such as,
name
,description
,slug
andsku
. Filtering on flow fields is not supported. - A maximum of 10 filters is allowed on a single request.
- A request containing filters must not exceed 8KB.
- A request can have at most ten levels of nested parentheses.
- Different endpoints may limit the amount of
|
(or) conditions that may be specified in a single filter.
Errors
An incorrectly formatted filter or an unsupported operator returns a 400
response with the following error:
{
"errors": [
{
"title": "Bad Request",
"detail": "Could not parse the supplied filter"
}
]
}
Performance
Filtered queries may be less performant than non-filtered queries, especially at scale (either with high frequency requests or with a high amount of data) and care should be taken when using them. In particular:
- The
like
operator can take a noticeably longer amount of time, especially when there is a leading wildcard or wildcard on both sides of the search term. - The
in
operator with a large number of options can also result in degraded performance.
Best practices are:
- Using
eq
when possible instead oflike
. - Adding another search operator can often narrow down the request. For example, adding
eq(status,paid)
, orgt(updated_at,<DATE>)
tolike(contact.name,<SEARCH>)
may have higher performance. - Storing a copy of the filtered results in a Custom APIs and querying that. The Custom API can be populated using a combination of batch processing or Events.
- Use
in
as opposed to|
for multiple values when possible. For example,filter=in(status,created,processing)
will often perform better thanfilter=eq(status,created)|eq(status,processing)
.
Legacy Filtering Syntax
Supported Endpoints
Legacy Filtering is currently supported on the following endpoints:
- Get all customers
- Get all orders
- Get a hierarchy's children in the latest release
- Get a hierarchy's nodes in the latest release
- Get a node in the latest release
- Get a product's children
- Get all hierarchies in the latest release
- Get all nodes in the latest release
- Get all products in the latest release
- Get a node's Children in the latest release
- Get all products in a hierarchy in the latest release
- Get all products by node in the latest release
- Get a hierarchy's children in a shopper catalog
- Get a hierarchy's nodes in a shopper catalog
- Get a product's child products in a shopper catalog
- Get all hierarchies in a shopper catalog
- Get all nodes in a shopper catalog
- Get all Products in a shopper catalog
- Get a node's children in a shopper catalog
- Get all products in a hierarchy in a shopper catalog
- Get all products in a node in a shopper catalog
- Get all catalog rules
- Get personal data erasure requests
- Get personal data logs
- Get personal data related data entries
- Get all user authentication info
- Get all files
You can filter results returned from the API using a standard URI format.
-
You can only filter on base-object attributes, such as,
name
,slug
andsku
. Filtering on flow fields is not supported. See the individual endpoint for a list of supported filter attributes and operators. -
A maximum of 10 filters is allowed on a single request.
-
A request containing filters must not exceed 8KB. This is equal to approximately 200 IDs when using an
in
filter. -
Passing an incorrectly formatted filter or using an unsupported operator returns a
400
response with the following error:{
"errors": [
{
"title": "Bad Request",
"detail": "Could not parse the supplied filter"
}
]
}
Supported Characters
As filters are passed as URL query string parameters, you must ensure all filters are URL safe and be strict about the characters that are used in a filter.
Characters | Can be used in filter? |
---|---|
A-Z (upper & lower case) | Yes |
0-9 | Yes |
$ - _ * . | Yes |
" " (space) | Yes (an unencoded + is also treated as a space). |
+ | Only when URL encoded (%2B ). |
Supported Date Formats
If you need to specify a date, specify the date in YYYY-MM-DD
format, for example 2022-04-27. You can also specify the
date as an integer number of milliseconds since 1970-01-01 00:00:00. This is also known as UNIX time. For example,
640912546584
.
URL Encoding Filters
We recommend URL encoding filters. For ease of use, you can encode the full filter,
so filter=eq(email,ron+1@swanson.com)
becomes filter=eq%28email%2Cron%2B1%40swanson.com%29
.
Performance
Filtered queries may be less performant than non-filtered queries, especially at scale (either with high frequency requests or with a high amount of data) and care should be taken when using them. In particular:
- The
like
operator can take longer, for example, when there is a leading wildcard or a wildcard on both sides of the search term. - The
in
operator with a large number of options can result in degraded performance.
Best practices are:
- Using
eq
when possible instead oflike
. - Adding another search operator to narrow down the request. For example, adding
eq(status,paid)
, orgt(updated_at,<DATE>)
tolike(contact.name,<SEARCH>)
may have higher performance. - Storing a copy of the filtered results in a Custom Data (Flows) and querying the flow. The flow can be populated using a combination of batch processing or Events.
Supported Operators
The following search operators are supported with the original filtering syntax.
Operator | Description |
---|---|
eq | Equals. Checks if the values of two operands are equal. If they are, the condition is true. |
like | Like. Checks if the operand contains the specified string. Wildcards are supported. |
in | In. Checks if the values are included in the specified, comma separated, list. If they are, the condition is true. Wildcards are not supported. |
gt | Greater than. Checks if the value of the first operand is greater than that of the second. If they are, the condition is true. |
ge | Greater than or equal to. Checks if the value of the first operand is greater than or equal to that of the second. If they are, the condition is true. |
lt | Less than. Checks if the value of the first operand is less than that of the second. If they are, the condition is true. |
le | Less than or equal to. Checks if the value of the first operand is less than or equal to that of the second. If they are, the condition is true. |
Multiple filters can be supplied at once seperated by a colon (:
) which will logically AND the results.
For more detail on filtering, see the Filtering section under each endpoint.