Filtering data

How to add filters to your requests

When you fetch a collection of resources, you can add filters to further refine the results, using the filter[q] query parameter.

Filters can be combined according to an "AND" logic to send requests that fetch collections of resources meeting all the criteria within a set of multiple filters (see example).

Each single filter query is built like this:

filter[q][{{predicate}}]={{value}}

Here, value stands for the value that the fetched resources have to match, while the predicateparameter has this format:

{{attributes}}_{{matcher}}

Where attributes is a set of one or more attributes and matcher represents the condition to be met by the query.

Attributes can be combined according to an OR logic and can belong to either the fetched resource or to one of its relationships (see example).

List of predicates

You can find here below the complete list of all possible predicates:

PredicateDescriptionExample

*_eq

The attribute is equal to the filter value, not including null values.

filter[q][name_eq]=red+handbag

*_eq_or_null

The attribute is equal to the filter value, including null values.

filter[q][name_eq_or_null]=red+handbag

*_not_eq

The attribute is not equal to the filter value, not including null values.

filter[q][status_not_eq]=prospect

*_not_eq_or_null

The attribute is not equal to the filter value, including null values.

filter[q][reference_not_eq_or_null]=CRM

*_matches

The attribute matches the filter value with "LIKE" operator (works with strings only).

filter[q][email_matches]=%@gmail.com

*_does_not_match

The attribute does not match the filter value with "LIKE" operator (works with strings only).

filter[q][email_does_not_match]=%@hotmail.com

*_matches_any

The attribute matches any of the filter values (comma-separated) with "LIKE" operator (works with strings only).

filter[q][email_matches_any]=%@gmail.com,%@hotmail.com

*_matches_all

The attribute matches all of the filter values (comma-separated) with "LIKE" operator (works with strings only).

filter[q][name_matches_all]=%Pink%,%Logo%

*_does_not_match_any

The attribute does not match any of the filter values (comma-separated) with "LIKE" operator (works with strings only).

filter[q][email_does_not_match_any]=%Pink%,%Logo%

*_does_not_match_all

The attribute matches none of the filter values (comma-separated) with "LIKE" operator (works with strings only).

filter[q][email_does_not_match_all]=%@gmail.com,%@hotmail.com

*_lt

The attribute is less than the filter value.

filter[q][amount_cents_lt]=2000

*_lteq

The attribute is less than or equal to the filter value.

filter[q][amount_cents_lteq]=2000

*_gt

The attribute is greater than the filter value.

filter[q][created_at_gt]=2019-01-01T01:00:00.000Z

*_gteq

The attribute is greater than or equal to the filter value.

filter[q][updated_at_gteq]=2019-01-02T10:30:00.000Z

*_present

The attribute is not null and not empty (works with strings only).

filter[q][description_present]=false

*_blank

The attribute is null or empty (works with strings only).

filter[q][description_blank]=true

*_null

The attribute is null.

filter[q][reference_null]=true

*_not_null

The attribute is not null.

filter[q][reference_not_null]=false

*_in

The attribute matches any of the filter values (comma-separated), not including null values.

filter[q][status_in]=placed,pending

*_in_or_null

The attribute matches any of the filter values (comma-separated), including null values.

filter[q][reference_in_or_null]=CRM,SAP

*_not_in

The attribute matches none of the filter values (comma-separated), not including null values.

filter[q][status_not_in]=approved,cancelled

*_not_in_or_null

The attribute matches none of the filter values (comma-separated), including null values.

filter[q][reference_not_in_or_null]=CRM,SAP

*_lt_any

The attribute is less than any of the filter values (comma-separated).

filter[q][amount_cents_lt_any]=1500,700,3400

*_lteq_any

The attribute is less than or equal to any of the filter values (comma-separated).

filter[q][amount_cents_lteq_any]=1500,700,3400

*_gt_any

The attribute is greater than any of the filter values (comma-separated).

filter[q][amount_cents_gt_any]=1500,700,3400

*_gteq_any

The attribute is greater than or qual to any of the filter values (comma-separated).

filter[q][amount_cents_gteq_any]=1500,700,3400

*_lt_all

The attribute is less than all of the filter values (comma-separated).

filter[q][amount_cents_lt_all]=1500,700,3400

*_lteq_all

The attribute is less than or equal to all of the filter values (comma-separated).

filter[q][amount_cents_lteq_all]=1500,700,3400

*_gt_all

The attribute is greater than all of the filter values (comma-separated).

filter[q][amount_cents_gt_all]=1500,700,3400

*_gteq_all

The attribute is greater or equal to all of the filter values (comma-separated).

filter[q][amount_cents_gteq_all]=1500,700,3400

*_not_eq_all

The attribute is equal to none of the filter values (comma-separated).

filter[q][amount_cents_not_eq_all]=1000,2000,3000

*_start

The attribute starts with the filter value (comma-separated, works with strings only).

filter[q][email_start]=patrick

*_not_start

The attribute does not start with the filter value (comma-separated, works with strings only).

filter[q][email_not_start]=mary

*_start_any

The attribute starts with any of the filter values (comma-separated, works with strings only).

filter[q][email_start_any]=patrick,mary

*_start_all

The attribute starts with all of the filter values (comma-separated, works with strings only).

filter[q][email_start_all]=patrick,pat

*_not_start_any

The attribute does not start with any of the filter values (comma-separated, works with strings only).

filter[q][email_not_start_any]=patrick,pat

*_not_start_all

The attribute starts with none of the filter values (comma-separated, works with strings only).

filter[q][email_not_start_all]=patrick,mary

*_end

The attribute ends with the filter value (works with strings only).

filter[q][email_end]=.com

*_not_end

The attribute does not end with the filter value (works with strings only).

filter[q][email_not_end]=.it

*_end_any

The attribute ends with any of the filter values (comma-separated, works with strings only).

filter[q][email_end_any]=.com,.it

*_end_all

The attribute ends with all of the filter values (comma-separated, works with strings only).

filter[q][email_end_all]=.com,gmail.com

*_not_end_any

The attribute does not end with any of the filter values (comma-separated, works with strings only).

filter[q][email_not_end_any]=.com,gmail.com

*_not_end_all

The attribute ends with none of the filter values (comma-separated, works with strings only).

filter[q][email_not_end_all]=.com,.it

*_cont

The attribute contains the filter value (works with strings only).

filter[q][name_cont]=unisex

*_not_cont

The attribute does not contains the filter value (works with strings only).

filter[q][name_not_cont]=black

*_cont_any

The attribute contains any of the filter values (comma-separated, works with strings only).

filter[q][name_cont_any]=black,white

*_cont_all

The attribute contains all of the filter values (comma-separated, works with strings only).

filter[q][name_cont_all]=black,sleeve

*_not_cont_all

The attribute contains none of the filter values (comma-separated, works with strings only).

filter[q][sku_code_not_cont_all]=TSHIRT,SXXX

*_jcont

The attribute contains a portion of the JSON used as filter value (works with object only).

filter[q][metadata_jcont]={"key": "value"}

*_true

The attribute is true.

filter[q][tax_included_true]=true

*_false

The attribute is false.

filter[q][tax_included_false]=true

In case of comma-separated sets of values (e.g. *_in, *_matches_all, *_start_any etc.), pay attention to avoid whitespaces before or after each comma.

Predicates commonly used to filter by string attributes (e.g. *_cont, *_start, etc.) don't work when trying to filter by ID.

Filterable fields

Please note that if you try to filter a collection of resources by a non-filterable field you don't get any error from the API which will respond with a 200 OK status code, returning the full unfiltered list.

Examples

Combining filters

The following request fetches a collection of SKUs that have been created within a specific time range:

curl -g -X GET \
  'https://yourdomain.commercelayer.io/api/skus?filter[q][created_at_gt]=2018-01-01&filter[q][created_at_lt]=2018-01-31' \
  -H 'Accept: application/vnd.api+json' \
  -H 'Authorization: Bearer your-access-token'

Combining filters and attributes

The following request fetches a collection of orders that have been created or updated after a specific date and that haven't been paid yet:

curl -g -X GET \
  'https://yourdomain.commercelayer.io/api/orders?filter[q][created_at_or_updated_at_gt]=2018-01-01&filter[q][payment_status_eq]=unpaid' \
  -H 'Accept: application/vnd.api+json' \
  -H 'Authorization: Bearer your-access-token'

When combining attribute make sure not to mix types (e.g. string and integer).

Using filters that belong to a resource relationship

The following request fetches a collection of SKUs whose name, code, or shipping category name starts with the string "TSHIRT" (as mentioned above, the attributeshipping_category_name here belongs to a relationship and filters the list of SKUs by the name of the associated shipping category):

curl -g -X GET \
  'https://yourdomain.commercelayer.io/api/skus?filter[q][name_or_code_or_shipping_category_name_start]=TSHIRT' \
  -H 'Accept: application/vnd.api+json' \
  -H 'Authorization: Bearer your-access-token'

*_blank and *_present predicates don't work when filtering on related resources fields (e.g. you cannot check the existence of a relationship using/api/customers?filter[q][customer_group_id_present]=true). You can use *_null and *_not_null instead.

The following request fetches the list of orders placed by a certain customer after a specific date:

curl -g -X GET \
  'https://yourdomain.commercelayer.io/api/customers/xYZkjABcde/orders?filter[q][created_at_gt]=2017-12-12&filter[q][status_eq]=placed' \
  -H 'Accept: application/vnd.api+json' \
  -H 'Authorization: Bearer your-access-token'

The following request fetches the list of line items of type skus associated with a specific order:

curl -g -X GET \
  'https://yourdomain.commercelayer.io/api/orders/KjajhXvYya/line_items?filter[q][item_type_eq]=skus' \
  -H 'Accept: application/vnd.api+json' \
  -H 'Authorization: Bearer your-access-token'

Last updated