--- title: WHERE description: Filter rows by dimensions before aggregation in a ShopifyQL query. api_version: 2026-07 source_url: html: 'https://shopify.dev/docs/api/shopifyql/latest/syntax/where' md: 'https://shopify.dev/docs/api/shopifyql/latest/syntax/where.md' api_name: shopifyql --- # WHERE Use `WHERE` to filter rows before aggregation. `WHERE` filters on dimensions, not metrics. To filter aggregated results, use [`HAVING`](https://shopify.dev/docs/api/shopifyql/latest/syntax/having). To set the reporting period for a query, use [`SINCE`, `UNTIL`, `DURING`](https://shopify.dev/docs/api/shopifyql/latest/syntax/since-until-during). Wrap string values in single quotes. A `WHERE` condition can [compare a column to a literal](#comparison-operators), check membership with [`IN`](#range-and-membership-operators), test inclusive ranges with [`BETWEEN`](#range-and-membership-operators), match [strings](#string-matching) or [arrays](#array-matching) with `CONTAINS`, combine conditions with [`AND` or `OR`](#logical-operators), or filter by related records with [`MATCHES`](#matches). Use the [segment query language](https://shopify.dev/docs/apps/build/shopifyql/segment-query-language-reference), a subset of ShopifyQL that uses only `WHERE`, to build customer segments from their attributes, save them in the [Shopify admin](https://help.shopify.com/manual/customers/customer-segmentation/customer-segments), and query them through the [GraphQL Admin API](https://shopify.dev/docs/api/admin-graphql/latest/queries/segments). **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 ```shopifyql 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) ``` *** ## Comparison 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 | ` = ` | Matches values equal to the right-hand side. Works with compatible scalar values. | | not equals | ` != ` | Matches values not equal to the right-hand side. Works with compatible scalar values. | | less than | ` < ` | Matches values less than the right-hand side. Use with ordered values such as numbers, money, dates, or text. | | less than or equal | ` <= ` | Matches values less than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text. | | greater than | ` > ` | Matches values greater than the right-hand side. Use with ordered values such as numbers, money, dates, or text. | | greater than or equal | ` >= ` | Matches values greater than or equal to the right-hand side. Use with ordered values such as numbers, money, dates, or text. | Examples ### 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 ```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 ```shopifyql FROM sessions SHOW sessions WHERE session_duration < 60 GROUP BY session_device_type DURING last_week ORDER BY sessions DESC ``` *** ## Range 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 | ` IN (, ...)` | Matches any compatible value in a list. | | NOT IN | ` NOT IN (, ...)` | Excludes any compatible value in a list. | | BETWEEN | ` BETWEEN AND ` | Matches values within an inclusive range. Use with ordered values such as numbers, money, dates, or text. | | NOT BETWEEN | ` NOT BETWEEN AND ` | Matches values outside an inclusive range. Use with ordered values such as numbers, money, dates, or text. | Examples ### 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 ```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 ```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 ``` *** ## Null 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 ### 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 ```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 ```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 ``` *** ## Logical 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 | ` AND ` | Requires both conditions to match. | | OR | ` OR ` | Requires either condition to match. | | NOT | `NOT ` | Negates a condition. | Examples ### 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 ```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 ```shopifyql FROM sessions SHOW sessions WHERE NOT (session_bounced = true) GROUP BY session_device_type DURING last_month ORDER BY sessions DESC ``` *** ## String matching Filter string values by prefix, suffix, or substring. | Option | Syntax | Description | | - | - | - | | STARTS WITH | ` STARTS WITH ` | Matches string-like values with the specified prefix. | | ENDS WITH | ` ENDS WITH ` | Matches string-like values with the specified suffix. | | CONTAINS | ` CONTAINS ` | Matches string-like values containing the specified substring, or arrays containing the specified element. | | NOT CONTAINS | ` NOT CONTAINS ` | Matches string-like values or arrays that do not contain the specified value. | Examples ### 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 ```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 ```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 ``` *** ## Array 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 ### 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 ```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 ```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 ```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 ``` *** ## Anniversary 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 ### 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 ```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 ```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 ``` *** ## MATCHES 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 ```shopifyql 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 ### 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 ```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 ```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 ``` ### Supported schemas You can use `MATCHES` when you query the following schemas. Each schema's reference lists the MATCHES expressions available when you query it: * [`customers`](https://shopify.dev/docs/api/shopifyql/latest/schemas/customers/customers#matches-expressions) * [`payments`](https://shopify.dev/docs/api/shopifyql/latest/schemas/finance_and_payments/payments#matches-expressions) * [`sales`](https://shopify.dev/docs/api/shopifyql/latest/schemas/sales_revenue/sales#matches-expressions) * [`sessions`](https://shopify.dev/docs/api/shopifyql/latest/schemas/sessions_and_behavior/sessions#matches-expressions) * [`store_credit_transactions`](https://shopify.dev/docs/api/shopifyql/latest/schemas/finance_and_payments/store_credit_transactions#matches-expressions) ***