Skip to main content

WHERE

Use WHERE to filter rows before aggregation. WHERE filters on dimensions, not metrics. To filter aggregated results, use HAVING. To set the reporting period for a query, use SINCE, UNTIL, DURING.

Wrap string values in single quotes. A WHERE condition can compare a column to a literal, check membership with IN, test inclusive ranges with BETWEEN, match strings or arrays with CONTAINS, combine conditions with AND or OR, or filter by related records with MATCHES.

Use the segment query language, a subset of ShopifyQL that uses only WHERE, to build customer segments from their attributes, save them in the Shopify admin, and query them through the GraphQL Admin API.

Note

Excluding values with !=, NOT IN, or NOT CONTAINS doesn't remove rows where the field is NULL. Add AND field IS NOT NULL to exclude rows with missing values.

Syntax

WHERE condition

condition:
column comparison_operator value
column [NOT] IN (value, ...)
column [NOT] BETWEEN low AND high
column STARTS WITH|ENDS WITH|[NOT] CONTAINS value
column IS [NOT] NULL|TRUE|FALSE
match_expression [NOT] MATCHES (parameter_list)

Anchor to Comparison operatorsComparison operators

Compare a column or function result to a literal value. Equality works with any compatible scalar value. Ordered comparisons require ordered values such as numbers, money, dates, or text.

OptionSyntaxDescription
equals<column> = <value>Matches values equal to the right-hand side. Works with compatible scalar values.
not equals<column> != <value>Matches values not equal to the right-hand side. Works with compatible scalar values.
less than<column> < <value>Matches values less than the right-hand side. Use with ordered values such as numbers, money, dates, or text.
less than or equal<column> <= <value>Matches values less than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text.
greater than<column> > <value>Matches values greater than the right-hand side. Use with ordered values such as numbers, money, dates, or text.
greater than or equal<column> >= <value>Matches values greater than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text.
Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM payments
SHOW net_payments
WHERE transaction_kind = 'sale'
GROUP BY payment_gateway
SINCE -30d
ORDER BY net_payments DESC

Anchor to Range and membership operatorsRange and membership operators

Match a value against a list, or test an inclusive range. Membership operators work with compatible scalar lists. Range operators require ordered values such as numbers, money, or dates.

OptionSyntaxDescription
IN<column> IN (<value>, ...)Matches any compatible value in a list.
NOT IN<column> NOT IN (<value>, ...)Excludes any compatible value in a list.
BETWEEN<column> BETWEEN <low> AND <high>Matches values within an inclusive range. Use with ordered values such as numbers, money, dates, or text.
NOT BETWEEN<column> NOT BETWEEN <low> AND <high>Matches values outside an inclusive range. Use with ordered values such as numbers, money, dates, or text.
Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM sales
SHOW orders, amount_spent_per_customer
WHERE number_of_products_bought_together BETWEEN 2 AND 4
GROUP BY product_title
SINCE startOfDay(-14d) UNTIL today
ORDER BY orders DESC

Anchor to Null and Boolean checksNull and Boolean checks

Filter by missing values or Boolean states. Null checks work with any nullable field. Boolean checks work with Boolean values.

OptionSyntaxDescription
IS NULLIS NULLValue is null.
IS NOT NULLIS NOT NULLValue is not null.
IS TRUEIS TRUEValue is true.
IS NOT TRUEIS NOT TRUEValue is not true, including false or null.
IS FALSEIS FALSEValue is false.
IS NOT FALSEIS NOT FALSEValue is not false, including true or null.
Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM sessions
SHOW sessions
WHERE landing_page_url IS NULL
GROUP BY referrer_source
SINCE startOfDay(-30d) UNTIL today
ORDER BY sessions DESC

Anchor to Logical operatorsLogical operators

Combine or negate conditions. AND is evaluated before OR, so group conditions with parentheses to control the order, for example WHERE a AND (b OR c).

OptionSyntaxDescription
AND<condition> AND <condition>Requires both conditions to match.
OR<condition> OR <condition>Requires either condition to match.
NOTNOT <condition>Negates a condition.
Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM sales
SHOW net_sales
WHERE is_pos_sale = false
AND sales_channel IS NOT NULL
GROUP BY sales_channel
SINCE startOfMonth(-1m) UNTIL endOfMonth(-1m)
ORDER BY net_sales DESC

Anchor to String matchingString matching

Filter string values by prefix, suffix, or substring.

OptionSyntaxDescription
STARTS WITH<column> STARTS WITH <value>Matches string-like values with the specified prefix.
ENDS WITH<column> ENDS WITH <value>Matches string-like values with the specified suffix.
CONTAINS<column> CONTAINS <value>Matches string-like values containing the specified substring, or arrays containing the specified element.
NOT CONTAINS<column> NOT CONTAINS <value>Matches string-like values or arrays that do not contain the specified value.
Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM sales
SHOW total_sales
WHERE product_title CONTAINS 'shirt'
GROUP BY product_type
DURING bfcm2020
ORDER BY total_sales DESC

Anchor to Array matchingArray matching

Check whether an array field contains a specific element with CONTAINS. The value you test must match the array's element type: use a string for a string array, a whole number for an integer array, or a decimal for a money array. Wrap string values in single quotes, and don't quote numeric or money values. String array matching is case-insensitive.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM sales
SHOW total_sales
WHERE order_tags CONTAINS 'New customers'
GROUP BY month
DURING this_year
ORDER BY month ASC

Anchor to Anniversary filtersAnniversary filters

Filter by recurring annual dates, such as signup or first-purchase dates. anniversary() compares the month and day of a date or datetime field with =, !=, or BETWEEN.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM customers
SHOW total_number_of_orders
WHERE anniversary(day) = today

Anchor to MATCHESMATCHES

Use MATCHES and NOT MATCHES to filter by collections of related records. A MATCHES filter keeps rows that have at least one related record matching the named parameters, joined by customer. A parameter can filter on a dimension or a metric. MATCHES is the only place you can filter by a metric in WHERE.

Syntax

WHERE match_expression MATCHES (parameter_list)
WHERE match_expression NOT MATCHES (parameter_list)
Note

Each parameter filters on a single field, and you can use each field only once in a MATCHES filter. Separate multiple fields with commas, not AND or OR.

Examples

ShopifyQL

View in adminView query results in the ShopifyQL editor
FROM customers
SHOW total_number_of_orders
WHERE storefront.collection_viewed MATCHES (id = 12345)
GROUP BY month
SINCE startOfYear(0y) UNTIL today
ORDER BY month ASC

Anchor to Supported schemasSupported schemas

You can use MATCHES when you query the following schemas. Each schema's reference lists the MATCHES expressions available when you query it:

Was this page helpful?