Query Predicates

Query Predicates by example

on standard and Custom Fields

// Compare a field's value to a given value
name = "Peter" // For exact match to "Peter". This does not perform substring match.
name != "Peter"
age < 42
age > 42
age <= 42
age >= 42
age <> 42

// Combine any two conditional expressions in a logical conjunction / disjunction
name = "Peter" and age < 42
name = "Peter" or age < 42

// Negate any other conditional expression
not (name = "Peter" and age < 42)

// Check whether a field's value is or is not contained in
// a specified set of values.
age in (42, 43, 44)
age not in (42, 43, 44)

// to be noted: 'in' is much more efficient than several '='
// prefer:
name in ("Peter", "Barbara")
// to:
name = "Peter" or name = "Barbara"

// Check whether an array contains all or any of a set of values
tags contains all ("a", "b", "c")
tags contains any ("a", "b", "c")

// Check whether an array is empty
tags is empty

// Check whether a field exists & has a non-null value
name is defined
name is not defined

// Descend into nested objects
dog(age < 7 and name = "Beethoven")

// Descend into nested arrays of objects
cities(zip > 10000 and zip < 20000)

// Query GeoJSON field within a circle
// The two first parameters are the longitude and latitude of the circle's center.
// The third parameter is the radius of the circle in meter.
geoLocation within circle(13.37770, 52.51627, 1000)

// To query for resources with Custom Fields
// enclose the Custom Field with 'custom(fields(<name>:<value>))'
// where <name> is as defined in the FieldDefinition and <value> is compliant to the FieldType of the Custom Field
// name: "description", FieldType: CustomFieldStringType
custom(fields(description="example description"))

on Product Attributes

Query for ProductProjections with Attribute values. The following examples only query additional Product Variants.

// for missing attribute
variants(not(attributes(name="attribute-name")))

// for single attribute value of TextType
variants(attributes(name="attribute-name" and value="attribute-value"))
// for multiple attribute values of TextType with same name
variants(attributes(name="attribute-name" and value in ("attribute-value-1", "attribute-value-2")))

// for single attribute value of LTextType
variants(attributes(name="attribute-name" and value(en="attribute-value")))
// for multiple attribute values of LTextType with same name
variants(attributes(name="attribute-name" and value(en="english-value" or de="german-value")))

// for EnumType or LocalizableEnumType
variants(attributes(name="attribute-name" and value(key="enum-key")))

// for MoneyType (currencyCode is required)
variants(attributes(name="attribute-name" and value(centAmount=999 and currencyCode="EUR")))
// for MoneyType with centAmount within a specific range (currencyCode is required)
variants(attributes(name="attribute-name" and value(centAmount > 999 and centAmount < 1001 and currencyCode="EUR")))

// for NumberType
variants(attributes(name="attribute-name" and value=999))
// for NumberType with value within a specific range
variants(attributes(name="attribute-name" and value > 999 and value < 1001 ))

// for DateType, TimeType, or DateTimeType
variants(attributes(name="attribute-name" and value="attribute-value"))
// for DateType, TimeType, or DateTimeType with a value within a specific range
variants(attributes(name="attribute-name" and value > "value-start" and value < "value-end"))

// for ReferenceType
variants(attributes(name="attribute-name" and value(typeId="reference-type-id" and id="reference-id")))

To search only the Master Variant, use masterVariant instead of variants:

// for single attribute value of TextType
masterVariant(attributes(name="attribute-name" and value="attribute-value"))

To search in all Product Variants you must include both masterVariant and variants predicates:

// for single attribute value of TextType
masterVariant(attributes(name="attribute-name" and value="attribute-value")) or variants(attributes(name="attribute-name" and value="attribute-value"))

To query for Products, you must enclose the examples with masterData(current({example})) or masterData(staged({example})).

// for current data
masterData(current(variants(attributes(name="attribute-name" and value=999))))
// for staged data
masterData(current(masterVariant(attributes(name="attribute-name" and value="attribute-value"))))

A query endpoint usually restricts predicates to only be allowed on a specified subset of a resource representation's fields. The documentation of the endpoint lists fields that can be used for constructing predicates.

If multiple predicates are specified via multiple where query parameters, the individual predicates are combined in a logical conjunction, just as if they had been specified in a single where query parameter and combined with and.

Example predicate for querying Products:

# decoded predicate
masterData(current(slug(en="peter-42") and name(en="Peter")))

# URL-encoded predicate
masterData%28current%28slug%28en%3D%22peter-42%22%29%20and%20name%28en%3D%22Peter%22%29%29%29

on Carts

The following fields on Cart can be used in Query Predicates: anonymousId, billingAddress, businessUnit, cartState, country, createdAt, createdBy, custom, customLineItems, customerEmail, customerGroup, customerId, deleteDaysAfterLastModification, discountCodes, id, inventoryMode, itemShippingAddresses, key, lastModifiedAt, lastModifiedBy, lineItems, locale, origin, paymentInfo, shipping, shippingAddress, shippingCustomFields, shippingInfo, shippingRateInput, store, taxCalculationMode, taxMode, taxRoundingMode, taxedPrice, totalPrice, version.

The following fields on Cart's LineItem can be used in Query Predicates: custom, discountedPrice, discountedPricePerQuantity, distributionChannel, id, name, price, productId, productKey, productType, quantity, state, supplyChannel, taxRate, variant.

The following fields on Cart's CustomLineItem can be used in Query Predicates: custom, discountedPrice, discountedPricePerQuantity, money, name, quantity, slug, state.

TaxedPrice fields that can be used in Query Predicates: totalNet, totalGross.

on Cart Discounts

The following fields on CartDiscount can be used in Query Predicates: createdAt, createdBy, custom, description, id, isActive, key, lastModifiedAt, lastModifiedBy, name, references, requiresDiscountCode, sortOrder, stackingMode, stores, target, validFrom, validUntil, value, version.

Example predicate for querying Cart Discounts:

// query for Cart Discounts with total price discount as target
"target(type="totalPrice")"

on Customers

The following fields on Customer can be used in Query Predicates: id, createdAt, lastModifiedAt, customerNumber, email, lowercaseEmail, stores, firstName, lastName, middleName, title, addresses, defaultShippingAddressId, defaultBillingAddressId, isEmailVerified, externalId, customerGroup, locale, salutation, key.

Example predicate for querying Customers:

# decoded predicate
lowercaseEmail="peter@example.com"

# URL-encoded predicate
lowercaseEmail%3D%22peter%40example.com%22

on Orders

The following fields on Order can be used in Query Predicates: createdAt, lastModifiedAt, completedAt, orderNumber, customerId, customerEmail, anonymousId, country, totalPrice, taxedPrice, shippingAddress, billingAddress, customerGroup, orderState, shipmentState, paymentState, syncInfo, returnInfo, lineItems, customLineItems, cart, paymentInfo, state, locale, inventoryMode, shippingRateInput, shippingInfo.

The following fields on Order's LineItem can be used in Query Predicates: custom, discountedPrice, discountedPricePerQuantity, distributionChannel, id, name, price, productId, productKey, productType, quantity, state, supplyChannel, taxRate, variant.

The following fields on Order's CustomLineItem can be used in Query Predicates: custom, discountedPrice, discountedPricePerQuantity, money, name, quantity, slug, state.

// query for Orders that are awaiting stock
shipmentState="Backorder"

// query for Orders created in August 2022
createdAt > "2022-08-01T00:00:00.000Z" and createdAt < "2022-09-01T00:00:00.000Z"

Input variables

Query predicates support the use of input variables to simplify working with query strings that contain dynamic values. Using input variables also eases log analysis because identical query use cases have identical where query parameter values.

Inside the Query Predicate string, references to input variables must be prefixed with a colon :.

All input variables referenced in the Query Predicate must be added to the URI as separate HTTP query parameters whose names must be prefixed with var.. The same input parameter can be passed multiple times to be used as an array of values.

The actual names of the input variables must consist of alphanumeric characters only.

Input variable examples

HTTP query using one input variable:

# decoded:
?where=firstName = :name&var.name=Peter
# URL-encoded:
?where=firstName%20%3D%20%3Aname&var.name=Peter

HTTP query using an array input variable:

# decoded:
?where=masterVariant(sku in :skus) or variants(sku in :skus)&var.skus=sku1&var.skus=sku2&var.skus=sku3
# URL-encoded:
?where=masterVariant%28sku%20in%20%3Askus%29%20or%20variants%28sku%20in%20%3Askus%29&var.skus=sku1&var.skus=sku2&var.skus=sku3

Referencing input variables in Query Predicates:

// Compare a field's value to a given input variable value
name = :name

// Check whether a field's value is or is not contained in
// a specified set of input variable values.
age in :ages
age in (:age1, :age2, :age3)

age not in :ages
age not in (:age1, :age2, :age3)

// Check whether an array contains all or any of a set of input variable values
tags contains all :tags
tags contains all (:tag1, :tag2, :tag3)

tags contains any :tags
tags contains any (:tag1, :tag2, :tag3)

// Referencing an input variable multiple times
masterVariant(sku in :skus) or variants(sku in :skus)

Performance considerations

Query predicates are translated to database queries whose efficiency depends on how well the database can use indexes.

Indexes are managed automatically by commercetools Composable Commerce. Some indexes are present on all projects, others are added dynamically. For example, if you add a custom field to your carts and start querying it, the system will add an index to the project to improve performance if it meets criteria like query frequency.

The automatic index creation needs to collect a significant amount of data to not optimize for outlier scenarios. That's why it can take up to two weeks before a new index is added.

Inefficient patterns

Not all Query Predicates can be easily supported with an index, so if possible avoid the following patterns on large datasets:

• Indexes on Product Attributes on the Products and Product Projections endpoints are not provided. Use the Product Projection Search endpoint instead or try to narrow down your result set with an additional restriction, for example on the productType or the state field.
• Fields nested inside arrays - the index becomes inefficient if it contains too many entries. For example, variants(attributes(name = "attribute-name" and value = "attribute-value")).
• Querying for a condition that is true for the majority of resources, for example custom(state = "Done").
• Negations, such as state != "Open" or state is not defined.
• The empty operator on arrays, such as lineItems is empty or lineItems is not empty.

Efficient patterns

The following patterns are supporting efficient query execution:

• Non-nested fields that heavily reduce the subset of resources to filter, for example custom(state = "WaitingForExternalApproval") (assuming there are few resources waiting for external approval)
• If possible, prefer equality over other operators. For example, (state = "Done" or state = "Canceled)" can be faster than (state != "Open") in a query that contains further expressions.
• Queries on Orders, Carts, Customers, etc. may be fast in the beginning, but slow down over time as your Project grows. Include a time range, for example lastModifiedAt > $1-week-ago and ... (replace $1-week-ago with an actual date). Try defaulting the time range to the smallest value that is acceptable for the use case. Alternatively, try filtering by a field value that naturally only occurs in recently created resources.

Sorting and query performance

Sorting can also be supported by indexes. For best performance, the same index can be used for filtering and sorting. If possible, re-use a field from the Query Predicate for sorting.

For example, if your filter query is lastModifiedAt > $1-week-ago, sorting on lastModifiedAt is advised since it is more performant than sorting on a different field, like id.

Deactivate calculating the total

Deactivating the calculation of the total field in the PagedQueryResult will improve the performance of the query. Whenever the total is not needed, deactivate its calculation by using the query parameter withTotal=false.

Using predicates in conditional API Extensions

Besides querying resources, the predicates syntax also allows you to define complex expressions for the conditional execution of API Extensions.

Unsupported operators

A few minor differences aside, the behavior of the language and the operators used are the same for both querying APIs and defining API Extension conditions. The features not supported in conditional API Extensions are:

• Input variables
• The within-circle operator

Using predicates to react to change in data

In addition to the existing set of predicates, conditional API Extensions support the ability to check for changes to a resource's properties. For example, if your Extension is configured to be triggered for update actions to Carts, you can check whether the update action contains changes to the Cart's shipping information. By using the has changed operator, you can ensure that the Extension is called only when certain properties are updated in the update action triggering the Extension. Given a valid predicate, the has changed operator evaluates to true for all create actions.

The negation of has changed is also supported. Using has not changed ensures that the Extension only triggers when certain properties do not change during the update action. Given a valid predicate, the has not changed operator evaluates to false for all create actions.

Below are a few examples on how the has changed and has not changed operators behave during create and update actions:

// Evaluates to true if the name field is updated during the API call
name has changed

// Evaluates to true if the name field is not updated during the API call
name has not changed

// Evaluates to true if the resource is created with the name attribute
name has changed

// Evaluates to true if the quantities of existing Line Items change, or if new Line Items are added
lineItems(quantity has changed)

// Evaluates to true if a Line Item is added, removed, or updated
lineItems has changed

// Evaluates to false if the field shoeSize does not exist
shoeSize has changed

This feature requires a comparison between the current and previous versions of a resource during an update or create action. Therefore, the has changed and has not changed operators are only supported when defining API Extension conditions.

To learn more about using Query Predicates with API Extensions, see the Implementing an API Extension tutorial.