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 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 user authentication info
• Get all customers
• Get all orders
• Get personal data erasure requests
• Get personal data logs
• Get personal data related data entries
• Get all promotions
• 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 a nodes children
• Get a nodes products
• Get all price books
• Get all price modifiers
• Get all prices in a price book
• Get all products
• Get all entries
• Get all files
• Get Jobs
• Get all Custom APIs
• Get all Custom Fields
• Get all Custom API Entries

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 and sku. 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"
       }
     ]
  }
  

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.

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.

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.

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:

1. The like operator can take longer, for example, when there is a leading wildcard or a wildcard on both sides of the search term.
2. The in operator with a large number of options can result in degraded performance.

Best practices are:

1. Using eq when possible instead of like.
2. Adding another search operator to narrow down the request. For example, adding eq(status,paid), or gt(updated_at,<DATE>) to like(contact.name,<SEARCH>) may have higher performance.
3. 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.

The following search operators are supported with the original filtering syntax.

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.

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.

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.

Multiple filters can be supplied at once seperated by a colon (:) which will logically AND the results.

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.

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")

As filters are supplied in the URL, they need to be URL encoded before being sent. In most cases your User Agent should handle this automatically.

You can use both Dates and Timestamps in the filters and pass them in as arguments.

• You can only filter on base object attributes, such as, name, description, slug and sku. 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.

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"
    }
  ]
}

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:

1. 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.
2. The in operator with a large number of options can also result in degraded performance.

Best practices are:

1. Using eq when possible instead of like.
2. Adding another search operator can often narrow down the request. For example, adding eq(status,paid), or gt(updated_at,<DATE>) to like(contact.name,<SEARCH>) may have higher performance.
3. Storing a copy of the filtered results in a Custom Data (Flows) and querying that. The flow can be populated using a combination of batch processing or Events.