Choose a version:

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.

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.

Option	Syntax	Description
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

Examples

Description
Compare payment gateways by [`net_payments`](/docs/api/shopifyql/latest/schemas/finance_and_payments/payments#paymentsmetric-propertydetail-netpayments) over the last 30 days, counting only sale transactions. This example uses `WHERE` to filter raw rows before aggregation, so refunds and other transaction kinds never enter the totals.
ShopifyQL
```
FROM payments
  SHOW net_payments
  WHERE transaction_kind = 'sale'
  GROUP BY payment_gateway
  SINCE -30d
  ORDER BY net_payments DESC
```
Description
Count [`sessions`](/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) shorter than 60 seconds last week, broken down by device type. This example uses a `<` comparison in `WHERE` to filter on a numeric field before the rows are aggregated.
ShopifyQL
```
FROM sessions
  SHOW sessions
  WHERE session_duration < 60
  GROUP BY session_device_type
  DURING last_week
  ORDER BY sessions 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.

Option	Syntax	Description
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

Examples

Description
Break down [`orders`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-orders) and `amount_spent_per_customer` by product over the last 14 days, limited to products bought together in bundles of two to four. This example uses `BETWEEN` to filter `number_of_products_bought_together`, including both two and four.
ShopifyQL
```
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
```
Description
Compare payment methods by [`net_payments`](/docs/api/shopifyql/latest/schemas/finance_and_payments/payments#paymentsmetric-propertydetail-netpayments) last month, excluding the `shopify_payments` and `shopify_pay` gateways. This example uses `NOT IN` to drop every row whose gateway matches one of the listed values.
ShopifyQL
```
FROM payments
  SHOW net_payments
  WHERE payment_gateway NOT IN ('shopify_payments', 'shopify_pay')
  GROUP BY payment_method
  DURING last_month
  ORDER BY net_payments 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.

Option	Syntax	Description
IS NULL	`IS NULL`	Value is null.
IS NOT NULL	`IS NOT NULL`	Value is not null.
IS TRUE	`IS TRUE`	Value is true.
IS NOT TRUE	`IS NOT TRUE`	Value is not true, including false or null.
IS FALSE	`IS FALSE`	Value is false.
IS NOT FALSE	`IS NOT FALSE`	Value 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

Examples

Description
Count [`sessions`](/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) with no recorded landing page over the last 30 days, grouped by referrer source. This example uses `IS NULL` to keep only rows missing that field.
ShopifyQL
```
FROM sessions
  SHOW sessions
  WHERE landing_page_url IS NULL
  GROUP BY referrer_source
  SINCE startOfDay(-30d) UNTIL today
  ORDER BY sessions DESC
```
Description
Compare payment methods by [`net_payments`](/docs/api/shopifyql/latest/schemas/finance_and_payments/payments#paymentsmetric-propertydetail-netpayments) this quarter, counting only Shop Pay transactions. This example uses `IS TRUE` to filter on a boolean field directly, without comparing it to a literal.
ShopifyQL
```
FROM payments
  SHOW net_payments
  WHERE is_shop_pay_transaction IS TRUE
  GROUP BY payment_method
  DURING this_quarter
  ORDER BY net_payments 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).

Option	Syntax	Description
AND	`<condition> AND <condition>`	Requires both conditions to match.
OR	`<condition> OR <condition>`	Requires either condition to match.
NOT	`NOT <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

Examples

Description
Break down last month's [`net_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-netsales) by sales channel for non-point-of-sale orders that have a channel set. This example uses `AND` to keep only rows that satisfy both conditions, excluding point-of-sale orders and rows with no channel.
ShopifyQL
```
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
```
Description
Count last month's non-bounced [`sessions`](/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) by device type. This example uses `NOT` to negate the condition it wraps, so bounced sessions are excluded.
ShopifyQL
```
FROM sessions
  SHOW sessions
  WHERE NOT (session_bounced = true)
  GROUP BY session_device_type
  DURING last_month
  ORDER BY sessions DESC
```

Anchor to String matchingString matching

Filter string values by prefix, suffix, or substring.

Option	Syntax	Description
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

Examples

Description
Break down [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) during BFCM 2020 by product type, limited to products whose title mentions shirts. This example uses `CONTAINS` to match the substring anywhere in the title, so `T-shirt` and `Long-sleeve shirt` both qualify.
ShopifyQL
```
FROM sales
  SHOW total_sales
  WHERE product_title CONTAINS 'shirt'
  GROUP BY product_type
  DURING bfcm2020
  ORDER BY total_sales DESC
```
Description
Count [`sessions`](/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#sessionsmetric-propertydetail-sessions) over the last seven days that landed on a secure URL, grouped by landing page type. This example uses `STARTS WITH` to match only at the beginning of the value, anchoring on the `https://` prefix.
ShopifyQL
```
FROM sessions
  SHOW sessions
  WHERE landing_page_url STARTS WITH 'https://'
  GROUP BY landing_page_type
  SINCE -7d UNTIL today
  ORDER BY sessions 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

Examples

Description
Summarize [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) by month this year for orders tagged `New customers`. This example shows how `CONTAINS` on an array field like `order_tags` matches when any element equals the value.
ShopifyQL
```
FROM sales
  SHOW total_sales
  WHERE order_tags CONTAINS 'New customers'
  GROUP BY month
  DURING this_year
  ORDER BY month ASC
```
Description
Show the 10 product titles with the highest [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) last month among orders that also included product ID `12345`. This example uses `CONTAINS` to check for that ID inside the `products_bought_together_ids` array.
ShopifyQL
```
FROM sales
  SHOW total_sales
  WHERE products_bought_together_ids CONTAINS 12345
  GROUP BY product_title
  DURING last_month
  ORDER BY total_sales DESC
  LIMIT 10
```
Description
Show the 10 product titles with the highest [`total_sales`](/docs/api/shopifyql/latest/schemas/sales_revenue/sales#salesmetric-propertydetail-totalsales) last month among orders that also included a variant priced at `10.2`. This example uses `CONTAINS` to check for that decimal value inside the `variants_bought_together_variant_prices` array.
ShopifyQL
```
FROM sales
  SHOW total_sales
  WHERE variants_bought_together_variant_prices CONTAINS 10.2
  GROUP BY product_title
  DURING last_month
  ORDER BY total_sales DESC
  LIMIT 10
```

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

Examples

Description
Total [`total_number_of_orders`](/docs/api/shopifyql/latest/schemas/customers/customers#customersmetric-propertydetail-totalnumberoforders) from customers whose first purchase shares today's month and day. This example uses `anniversary()`, which compares only month and day and ignores the year.
ShopifyQL
```
FROM customers
  SHOW total_number_of_orders
  WHERE anniversary(day) = today
```
Description
Summarize [`total_amount_spent`](/docs/api/shopifyql/latest/schemas/customers/customers#customersmetric-propertydetail-totalamountspent) by month across 2026 from customers whose first order falls anywhere in March. This example uses `BETWEEN` with `anniversary()` to match a month-and-day range in any year, so any first order from March 1 through March 31 qualifies.
ShopifyQL
```
FROM customers
  SHOW total_amount_spent
  WHERE anniversary(day) BETWEEN 2026-03-01 AND 2026-03-31
  GROUP BY month
  SINCE 2026-01-01 UNTIL 2026-12-31
  ORDER BY month ASC
```

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.

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

Examples

Description
Find [`total_number_of_orders`](/docs/api/shopifyql/latest/schemas/customers/customers#customersmetric-propertydetail-totalnumberoforders) by month this year from customers who viewed product page ID `12345`. This example uses `MATCHES` to filter customers on a condition over their related `storefront.product_viewed` records, without joining tables.
ShopifyQL
```
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
```
Description
Find [`total_number_of_orders`](/docs/api/shopifyql/latest/schemas/customers/customers#customersmetric-propertydetail-totalnumberoforders) by month this year from customers who viewed a product page more than twice. This example uses `MATCHES (count > 2)` to filter on each customer's view count for their related `storefront.product_viewed` records.
ShopifyQL
```
FROM customers
  SHOW total_number_of_orders
  WHERE storefront.product_viewed MATCHES (count > 2)
  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?