Using the ShopifyQL query editor

You can use ShopifyQL with the new Shopify Analytics to explore your own business's database and retrieve the data that gives you a more in-depth understanding of your business.

ShopifyQL, or Shopify Query Language, is Shopify’s query language built for commerce. Query languages are used to request and retrieve data from databases. Your store’s data is stored in database tables, structured in defined columns and rows. Columns define the type of information they contain, such as sales, and rows specify the actual value of the data type, such as $2,450 USD in sales.

To retrieve your data in a meaningful format, a query must be submitted to the database. A query is a question that’s asking for specific data as an answer, made of keywords and their corresponding parameters. The combination of several keywords with specific parameters builds your query. After you’ve built your query, you can run it and receive a report.

For a full list of available values and dimensions that can be used in ShopifyQL report queries, refer to the Orders dataset and the Products dataset.

Glossary of terms

Definitions of ShopifyQL terms, such as dimension, keyword, and operator.
Term	Definition
Dimension	An attribute that segments data so that it can be sorted and presented more clearly. Common examples of dimensions include time, products, and places. Dimensions are used as parameters in ShopifyQL.
Keyword	ShopifyQL syntax that acts as a command to direct your query.
Metric	A quantitative measurement of data. Common examples of metrics include total sales, number of orders, and gross profit.
Parameter	ShopifyQL syntax that identifies the database elements or details to be included in your query.
Operator	A reserved word or character that's used as part of a query. Examples include `STARTS WITH`, `>=` or `last_week`.

ShopifyQL syntax

You must meet the following requirements when crafting a valid report query using ShopifyQL:

You can place an entire query on one line, or on separate lines.
You must include at least the FROM and SHOW keywords, with their appropriate parameters. All other keywords and parameters are optional.
All keywords in a query must be included in the following order:
1. FROM
2. SHOW
3. WHERE
4. GROUP BY
5. WITH TOTALS, GROUP_TOTALS, PERCENT_CHANGE
6. TIMESERIES
7. HAVING
8. SINCE & UNTIL, or DURING
9. COMPARE TO & optional UNTIL
10. ORDER BY
11. LIMIT
12. VISUALIZE & TYPE

This is an example of ShopifyQL, written out as a query using the correct syntax. The keywords are bolded, their corresponding parameters are included using generic placeholders, and optional parameters are in brackets:

FROM table_name1, table_name2, ...
SHOW column1 { AS alias }, column2 { AS alias }, ...
WHERE condition
GROUP BY dimension
TIMESERIES time_dimension
WITH TOTALS, GROUP_TOTALS, PERCENT_CHANGE
HAVING condition
SINCE date_offset
UNTIL date_offset
DURING named_date_range
COMPARE TO [ date_offset, ...]
ORDER BY column { ASC | DESC }
LIMIT number { OFFSET number }
VISUALIZE [ alias1, alias2, ... ]
  TYPE { visualization_type }
  LIMIT number

ShopifyQL keywords

ShopifyQL queries can range from basic, for high-level data insights, to comprehensive, for detailed insights. Each keyword has a specific function that builds out your query.

List of ShopifyQL keywords and their descriptions.
Keyword	Description
`FROM`	Specifies which dataset table to select data from.
`SHOW`	Selects the columns you want to extract from the dataset table.
`WHERE`	Defines the condition or conditions that rows must satisfy to be selected.
`GROUP BY`	Groups the extracted data by a dimension or time dimensions.
`WITH`	Modifies the behavior of certain ShopifyQL keywords.
`TIMESERIES`	Distinguishes grouping by time dimensions and backfills dates in a query.
`HAVING`	Filters the results of a query after they've been grouped.
`SINCE`	Shows data since a specified time in the past. Often paired with `UNTIL`.
`UNTIL`	Shows data until a specified time in the past. Often paired with `SINCE` or `COMPARE TO`.
`DURING`	Shows data during a specified time in the past.
`COMPARE TO`	Shows data for comparison to a specified time in the past.
`ORDER BY`	Specifies which column to order the data by.
`LIMIT`	Limits the number of rows of data that are shown.
`VISUALIZE`	Displays your data in a line or bar visualization. You can specify which visualization you want with `TYPE`.
`AS`	An optional keyword that renames a column to a name of your choosing.

FROM and SHOW

Creating the simplest ShopifyQL query only requires two keywords: FROM and SHOW, written in that order. FROM, followed by one or more table name parameters specifies which tables you want to query. SHOW, followed by any number of column name parameters specifies the columns you want to select.

SHOW can be used to specify the order in which metrics and dimensions are returned in the report.

For example, you can return the sum of total sales by writing this query:

FROM sales
SHOW total_sales

WHERE

The WHERE keyword allows you to apply a dimensional filter to an entire ShopifyQL query. The filter can be modified by both comparison operators (such as greater than >), logical operators (such as AND or NOT), and partial string and array matching (such as STARTS WITH and CONTAINS).

WHERE conditions must meet the following requirements:

Values must be wrapped in single quotes ('), not double quotes (").
Conditions can't contain any arithmetic.
Conditions can only reference a dimension, not a metric.

For example, if you want to return total sales but filtered based on billing country, then your query is:

FROM sales
SHOW total_sales, product_title, product_type, product_vendor
WHERE billing_country='Canada'

Such as in the example above, you can filter the result set with the WHERE parameter even when that parameter isn't included in the SHOW keyword. In this case, total sales is filtered for orders from Canada only, even though billing_country isn't included in the result set.

Comparison operators

The WHERE keyword uses comparison operators to filter data. In the example above, = was used to specify that the query filters on a specific value, however, there are other operators available to you:

List of ShopifyQL comparison operators, such as greater than or equal to.
Comparison operator	Description
=	equal to
!=	not equal to
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

Logical operators

To further filter your data, you can add logical operators to your query. The ShopifyQL logical operators are:

List of ShopifyQL logical operators, such as AND, OR, and NOT.
Logical operator	Description
`AND`	Filter to include all rows where the conditions separated by `AND` are satisfied.
`OR`	Filter to include all rows where either of the conditions separated by `OR` are satisfied.
`NOT`	Filter to only include rows where the conditions aren't satisfied, such as rows that don’t contain the specified value.

You can use multiple filters with the WHERE keyword by adding logical operators.

Adding to the example dataset query, to get a monthly grouping of net sales for all orders with dried peaches where the billing address was in Canada and there was a discount applied, the query is:

FROM sales
SHOW total_sales, product_title, product_type, product_vendor
WHERE billing_country='Canada' AND product_title='Dried Peaches' AND discounts > 0

GROUP BY

To segment a metric by a dimension, such as group sales by region, use the GROUP BY keyword. The GROUP BY keyword can be paired with any dimension parameter.

For example, a query that groups total sales by billing country and region is written as:

FROM sales
SHOW total_sales
GROUP BY billing_country, billing_region

Here is another example query that uses a time dimension to display total sales by month:

FROM sales
SHOW total_sales
GROUP BY month

The above query doesn't return any months where you have no sales. If you want a query that returns a complete, uninterrupted time period, then use the TIMESERIES keyword.

FROM sales
SHOW total_sales
TIMESERIES month
SINCE last_year
UNTIL today

Time Dimensions

These are the time dimensions that you can use to group your data:

List of ShopifyQL time dimensions, such as second, minute, or day of week.
Operator	Description
`second`	Grouping by second of hour.
`minute`	Grouping by minute of hour.
`hour`	Grouping by hour of calendar day.
`day`	Grouping by calendar day.
`week`	Grouping by calendar week.
`month`	Grouping by calendar month.
`quarter`	Grouping by calendar quarter.
`year`	Grouping by calendar year.
`hour_of_day`	Grouping by 24 hours (1, 2, ..., 24).
`day_of_week`	Grouping by day of week (M, T, W, ..., S).
`week_of_year`	Grouping by week of year (1, 2, ..., 52).
`month_of_year`	Grouping by month of year (1, 2, ..., 12).

TIMESERIES

You can use the TIMESERIES keyword when you want to distinguish grouping by time dimensions, view metrics over time, and backfill dates in a query to turn into a timeseries graph where data doesn't exist.

In this example, you're backfilling missing total sales data in the last 15 days:

FROM sales
SHOW total_sales
GROUP BY product_title, billing_country
TIMESERIES day
SINCE -15d

TIMESERIES and the order of columns

Depending on whether or not the TIMESERIES is given and present in either the GROUP BY or SHOW values, the order of columns can change.

Examples of TIMESERIES and how the order of columns displays with GROUP BY or SHOW in ShopifyQL.
`TIMESERIES`	`SHOW`	`GROUP BY`	Result
Given	Not present	Not present	The `TIMESERIES` is the first dimension.
Given	Not present	Present	Time dimensions position is defined by it’s position in `GROUP BY`.
Given	Present	Present	Time dimensions position is defined by it’s position in `SHOW`.
Not given	Not present	Present	Time dimensions position is defined by it’s position in `GROUP BY`. Data isn't backfilled.
Not given	Present	Present	Time dimensions position is defined by it’s position in `SHOW`. Data isn't backfilled.
Not given	Present	Not present	Triggers a syntax error because `SHOW` can only reference dimensions present in `GROUP BY`.

HAVING

Similar to WHERE, you can use the HAVING keyword to filter the results of a query after they've been grouped. The filter can have one condition, but the condition can be modified by both comparison operators (such as greater than >) and logical operators (such as AND or NOT). Unlike the WHERE keyword, HAVING can reference aliases, aggregate functions, and grouping columns.

HAVING requires your query to include a GROUP BY or TIMESERIES clause because HAVING filters the results of a query after they've been grouped by GROUP BY or TIMESERIES.

In this example, you're filtering the total sales for each product that have total sales greater than 1000 and less than 5000:

FROM sales
SHOW total_sales
GROUP BY product_title
HAVING total_sales > 1000 AND total_sales < 5000

WITH

The WITH keyword modifies the behavior of certain other ShopifyQL keywords. There are 3 available modifications:

TOTALS: Provides a top-level summary of metrics before they're broken down by any dimension.
GROUP_TOTALS: Provide a total for all sub-groupings when there is a group by aggregation.
PERCENT_CHANGE: Adds a percent change metric to each comparison column in COMPARE TO. When this modifier is present, a new column is added for each comparison metric column, containing the percent change.

TOTALS

The TOTALS modifier provides a top-level summary of metrics before they're broken down by any other dimension. When you use WITH TOTALS, the query returns the totals as extra columns in the results set with the metric name and "totals" as part of the column name. Each row contains all applicable totals.

For example, this query displays the totals of total sales:

FROM sales
SHOW gross_sales, net_sales, total_sales
TIMESERIES day
WITH TOTALS

This query might return a report similar to this:

Example ShopifyQL table of sales using WITH TOTALS.
Day	Gross sales	Net sales	Total sales	Gross sales totals	Net sales totals	Total sales totals
2024-01-01	1	4	7	6	15	24
2024-01-02	2	5	8	6	15	24
2024-01-03	3	6	9	6	15	24

GROUP_TOTALS

The GROUP_TOTALS modifier provides a total for all sub-groupings when there's a group by aggregation. When you use WITH GROUP_TOTALS, the query returns the totals as extra columns in the results set with the metric name, dimensions being totaled, and "totals" as part of the column name. Each row contains all applicable totals.

For example, this query displays a total of total sales by billing country:

FROM sales
SHOW customer_id, total_sales
GROUP BY customer_id, billing_country
WITH TOTALS, GROUP_TOTALS

This query might return a report similar to this:

Example ShopifyQL table of sales using GROUP_TOTALS.
Country	Customer ID	Total sales	Total sales country totals	Total sales totals
US	1	1	0	1
US	null	-1	1	1
Canada	1	1	1	1

PERCENT_CHANGE

The PERCENT_CHANGE modifier adds a percentage change metric to each comparison column when you use COMPARE TO. When you use WITH PERCENT_CHANGE, the query returns the percentage change for each comparison metric as extra columns in the results set with the metric name and "percent change" as part of the column name.

The formula used for calculating percentage change is: (base_column - comparison_column) * 100 / abs(comparison_column)

For example, this query uses SINCE and COMPARE TO to compare the net sales per day in the previous month to the same month in the previous year, with a column for percent change:

FROM sales
SHOW net_sales
TIMESERIES day
WITH PERCENT_CHANGE
SINCE -1m
UNTIL -0m
COMPARE TO previous_year

The columns in the resulting report include day, net sales, comparison day, comparison net sales, and percent change net sales.

SINCE and UNTIL

If you want to filter your query by a date or some time period, then you can use the SINCE and UNTIL keywords and their associated parameters. These keywords are unique because they only filter time periods. If you use SINCE and don't define an UNTIL value, then the end of your time range defaults to today.

For example, this is a query to find net sales over the last 12 months, in Canada, ending with yesterday:

FROM orders
SHOW net_sales
WHERE billing_country = 'Canada'
GROUP BY month ALL
SINCE -12m
UNTIL yesterday

Offset operators

You can filter by specific dates, or by date and time offsets. The ShopifyQL offset operators are:

List of ShopifyQL offset operators, such as offsetting by seconds, minutes, or a specific date.
Offset operator	Description
`-{#}s`	Number of seconds ago from the date and time that the query is run.
`-{#}min`	Number of minutes ago from the date and time that the query is run.
`-{#}h`	Number of hours ago from the date and time that the query is run.
`-{#}d`	Number of days ago from the date and time that the query is run.
`-{#}w`	Number of weeks ago from the date and time that the query is run.
`-{#}m`	Number of months ago from the date and time that the query is run.
`-{#}q`	Number of quarters ago from the date and time that the query is run.
`-{#}y`	Number of years ago from the date and time that the query is run.
`yyyy-MM-dd`	A specific date.
`yyyy-MM-ddThh:mm:ss`	A specific date and time.

Date functions

You can use the following functions combined with any date range operators (with the exception of specific dates) in SINCE and UNTIL statements. The "startOf..." functions truncate to the start of the associated time unit (minute, hour, day, week, month, quarter, and year) when used with SINCE, and the "endOf..." functions truncate to the end of the associated time unit when used with UNTIL.

Date function units and operator units must match in order to return a valid result. For example, startOfMonth(-1m) is correct, but startOfMonth(-1d) is not.

List of ShopifyQL date functions, such as end of day, or start of quarter.
Date function	Description
`now`	The date and time when the query is run.
`startOfMinute(-{#}min)`	Truncates the date range operator to the beginning of the target minute.
`endOfMinute(-{#}min)`	Truncates the date range operator to the end of the target minute.
`startOfHour(-{#}h)`	Truncates the date range operator to the beginning of the target hour.
`endOfHour(-{#}h)`	Truncates the date range operator to the end of the target hour.
`startOfDay(-{#}d)`	Truncates the date range operator to the beginning of the target day.
`endOfDay(-{#}d)`	Truncates the date range operator to the end of the target day.
`startOfWeek(-{#}w)`	Truncates the date range operator to the beginning of the target week.
`endOfWeek(-{#}w)`	Truncates the date range operator to the end of the target week.
`startOfMonth(-{#}m)`	Truncates the date range operator to the beginning of the target month.
`endOfMonth(-{#}m)`	Truncates the date range operator to the end of the target month.
`startOfQuarter(-{#}q)`	Truncates the date range operator to the beginning of the target quarter.
`endOfQuarter(-{#}q)`	Truncates the date range operator to the end of the target quarter.
`startOfYear(-{#}y)`	Truncates the date range operator to the beginning of the target year.
`endOfYear(-{#}y)`	Truncates the date range operator to the end of the target year.

For example, if today is November 8, 2022, then you can use the following query to return the gross sales from January 1, 2020 to October 31, 2022:

FROM sales
SHOW gross_sales
SINCE startOfYear(-2y)
UNTIL endOfMonth(-1m)

DURING

The DURING keyword simplifies date filtering for date ranges, and replaces the use of SINCE and UNTIL. You can use the DURING keyword to filter the query results for a known time period, such as a calendar year or specific month, or for date ranges that have different dates every year, such as Black Friday Cyber Monday. For example:

FROM sales
SHOW total_sales
GROUP BY day
DURING bfcm2021

Named date range operators

DURING accepts any of the following named date range operators:

List of ShopifyQL named date range operators, such as today, yesterday, or this week.
Named date range operator	Description
`today`	The date when the query is run.
`yesterday`	The previous 24h period from the time query is run.
`this_week`	The current calendar week.
`this_month`	The current calendar month.
`this_quarter`	The current calendar quarter.
`this_year`	The current calendar year.
`last_week`	The previous calendar week.
`last_month`	The previous calendar month.
`last_quarter`	The previous calendar quarter.
`last_year`	The previous calendar year.
`bfcmYYYY`	The Black Friday Cyber Monday range for the specified year. For example, `bfcm2022` returns results for November 25-28, 2022.

COMPARE TO

The COMPARE TO keyword allows you to compare data across the date range in SINCE and UNTIL or DURING, and the one in COMPARE TO.

You can use the COMPARE TO keyword with the following types of parameters:

Absolute dates, such as 2023-01-01: Includes the metrics for the specified period and the comparison metrics for the same period on the absolute date.
Named dates, such as last_week: Includes the metrics for the specified period and the comparison metrics for the same period on the named date.
- The operator used by COMPARE TO doesn't have to be the same length in time as the one used by DURING. If no UNTIL value is provided, then it calculates the time range to be equal with the value specified in DURING. For example, DURING this_week COMPARE TO last_month compares this week's data to a one-week period that starts at the beginning of last month.
Offset dates, such as -3q: Includes the metrics for the specified period and the comparison metrics for the same period on the relative date.
Multiple date comparisons, such as -1y, -2y: Compare your data from a specific date range with multiple other date ranges. This can be useful when you want to track changes over several periods.
If you use a date dimension in a query with COMPARE TO, you must use TIMESERIES for it, rather than GROUP BY.

The following example compares net sales during the previous month to the same month in the previous year:

FROM sales
SHOW net_sales, product_title
GROUP BY product_title
TIMESERIES day
SINCE -1m
UNTIL -0m
COMPARE TO previous_year

Relative date range operators

Relative operators return the same length of time as the base date range, shifted back by the specified period. In addition to the named date range operators, COMPARE TO accepts the following relative operators:

List of ShopifyQL relative date range operators, such as previous period or previous year.
Relative date range operator	Description
`previous_period`	One period before the base date range.
`previous_year`	One year before the base date range.
`previous_quarter`	One quarter before the base date range.
`previous_month`	One month before the base date range.
`previous_week`	One week before the base date range.
`previous_day`	One day before the base date range.
`previous_hour`	One hour before the base date range.
`previous_minute`	One minute before the base date range.
`previous_second`	One second before the base date range.

ORDER BY

You can specify how you want to sort the data returned by your query using the ORDER BY keyword, and its parameters: ASC, for ascending order, and DESC, for descending order.

You can specify any metric or dimension you’re including in your query in the ORDER BY keyword, including multiple fields.

For example, this query returns net sales for all products and variants over the last year. The results are first sorted in alphabetical order by product title, and then in reverse alphabetical order by product type:

FROM sales
SHOW net_sales
GROUP BY product_title, product_type
SINCE -1y
UNTIL today
ORDER BY product_title, product_type DESC

The order in which you write your metrics or dimension matters. If you specify multiple values for ORDER BY, then the sorting is applied to each metric or dimension in the order given.

TIMESERIES and the order of columns

Depending on whether or not the query includes TIMESERIES and ORDER BY, the order of columns can change.

Example of TIMESERIES and the order of columns when ORDER BY is present using ShopifyQL.
`TIMESERIES`	`ORDER BY`	Result
Present	Not present	Results are ordered by `TIMESERIES` dimension.
Present	Present	Results are ordered by `TIMESERIES` time dimension, then `ORDER BY` dimension.
Not present	Present	Results are ordered by `ORDER BY` dimension.
Not present	Not present	Results are ordered by the first `SHOW` column.

LIMIT

The LIMIT keyword allows you to specify a maximum number of rows the query returns. This is useful when you simply want to understand how the data in each column displays, or for larger reports where queries can take longer to return values. You can combine LIMIT with ORDER BY to create top and bottom lists.

If you don't specify a LIMIT value, then the query defaults to 1000 rows.

You can also use an optional { OFFSET # } parameter to skip a certain number of rows before beginning to return row data. The resulting phrase would be formatted similar to: LIMIT 15 { OFFSET 5 }.

This example uses LIMIT and ORDER BY to create a list of top 10 selling products over the last 3 months:

FROM sales
SHOW gross_sales as total_gross_sales
GROUP BY product_title
SINCE -3m
UNTIL today
ORDER BY total_gross_sales DESC
LIMIT 10

VISUALIZE and TYPE

The VISUALIZE keyword allows you to write a ShopifyQL query that displays data in a graphical visualization. The supported visualizations include the following values:

bar
stacked_bar
stacked_horizontal_bar
line
simple_bar
stacked_area
single_metric
donut
list
list_with_dimension_values
horizontal_bar
cohort
single_stacked_bar
funnel
grouped_bar
horizontal_grouped_bar
table
grid

The TYPE keyword is optional and must be accompanied by a single type of visualization. If TYPE isn’t included in your query, then ShopifyQL automatically decides the best visualization that suits your query. If your query can’t be visualized as written, then ShopifyQL returns tabular data.

VISUALIZE also accepts an optional LIMIT keyword where the parameter is the limited number of data points to render.

For example, you can visualize your sales trend over the last year by month with a trend line. This query returns a time series chart displaying the gross sales by month over the last year. The gross sales are depicted by a single line, with the x-axis labeled as month and the y-axis labeled as gross sales:

FROM sales
SHOW gross_sales
TIMESERIES month
SINCE -1y
UNTIL today
VISUALIZE gross_sales TYPE line

AS

The keyword AS is an optional keyword that allows you to rename (or provide an alias for) a column or the return value of an aggregate function.

AS accepts only a single parameter. If the alias has a space in the name, then you must surround the alias with double quotes (").

FROM sales
SHOW total_sales AS "My Total Sales"

Other ShopifyQL functions and operators

ShopifyQL includes the following additional operators and functions:

Mathematical operators, such as + or /
Functions, such as round() and trim()
Implicit joins
Partial string and array matching, such as STARTS WITH and CONTAINS
Comments

Mathematical operators

ShopifyQL allows arithmetic operations with the metrics in your data. The following mathematical operators are available:

List of ShopifyQL mathematical operators, including plus and minus.
Mathematical operator	Description
+	Addition of two numbers.
-	Subtraction of two numbers.
*	Multiplication of two numbers.
/	Division of two numbers.

For example, this query calculates order value for each region over the last year. When using mathematical operators with metrics, you can use the AS keyword to assign a new name to the new metric.

FROM sales
SHOW (net_sales + returns) AS order_value, orders, (net_sales + returns)/orders AS sales_per_order
GROUP BY billing_region
SINCE -1y
UNTIL today

Functions

ShopifyQL functions allow you to aggregate columns, or combine them to create a new value, similar to pivot tables in Microsoft Excel. The following function operators are available in the current version of ShopifyQL:

List of supported ShopifyQL functions, such as trimming and rounding.
Function operator	Description
`TRIM(column_name)`	Removes leading and trailing whitespace from a string.
`ROUND(column_name, decimal_places)`	Rounds a numerical value to the nearest integer or specified decimal places. In this function, `decimal_places` is an integer value: If `decimal_places` > 0, then the function rounds the value to the right of the decimal point. If `decimal_places` < 0, then the function rounds the value to the left of the decimal point. If `decimal_places` = 0, then the function rounds the value to integer. In this case, the argument can be omitted entirely.

For example, this query uses the rounding function on the gross_sales column, but omits the decimal_places argument to round the value to the integer:

FROM sales
SHOW average_order_value, round(gross_sales)
GROUP BY billing_region
SINCE 2021-01-01
UNTIL 2021-12-31

Implicit joins

A join allows you to view metrics from different domains together, side by side. Joins are done implicitly and intelligently in ShopifyQL.

ShopifyQL has the following join capabilities:

ShopifyQL allows dimension field joins when there is a single FROM table in the query.
Automatic left join on dimension fields.
ShopifyQL allows multi-fact joins (when there are multiple FROM tables in the queries).
Automatic full join on multi-fact joins, which support any number of tables or schemas and grouped by dimensions.
Metrics in multi-fact joins can use functions and math.

Shopify QL has the following join restrictions:

Join field must have the same name in all joined schemas.
Join field must be in GROUP BY.
Join field can't use functions or math.
FROM is necessary for multi-fact joins to work.
In a multi-fact join, every GROUP BY must be in all schemas and is considered a field on which to be joined.

For example, this query uses a multi-fact join:

FROM sales, sessions
SHOW day, total_sales, sessions
GROUP BY day
SINCE 2023-10-03
ORDER BY day

Partial string and array matching

You can use the following operators for partial string and array matching:

List of Partial string and array matching operators in ShopifyQL.
Operator	Description
`STARTS WITH`	Return all rows where a column starts with a prefix.
`ENDS WITH`	Return all rows where a column ends with a suffix.
`CONTAINS`	Return all rows where a column contains a part of a string, or an element in an array.

Some examples of partial string matching using operators include the following queries:

FROM sales
SHOW product_title
WHERE product_title STARTS WITH 'Summer'
GROUP BY product_title

FROM sales
SHOW product_title
WHERE product_title ENDS WITH 'kit'
GROUP BY product_title

FROM sales
SHOW product_title
WHERE product_title CONTAINS 'Beef'
GROUP BY product_title

The CONTAINS operator can be used to match elements within arrays, including integers, strings, and decimals. This matching isn't case-sensitive. Some examples of array matching using CONTAINS include the following queries:

FROM sales
SHOW orders
WHERE products_bought_together_ids CONTAINS 101

FROM customers
SHOW total_number_of_orders
WHERE customer_cities CONTAINS 'seattle'

FROM sales
SHOW orders
WHERE variants_bought_together_variant_prices CONTAINS 10.2

Comments

You can use comments to explain sections of ShopifyQL statements, or to prevent the execution of a ShopifyQL statement. Any text within a comment will be ignored during execution time.

Single line comments start with -- and end at the end of the line.

Multi-line comments start with /* and end with */.

FROM sales
SHOW average_order_value, gross_sales
-- the line below has been commented out and won't run
-- GROUP BY billing_region
WHERE billing_country = 'United States'
/*
this line and the two lines below it have been commented out and won't run
SINCE 2021-01-01
UNTIL 2021-12-31
*/

Using the ShopifyQL query editor

On this page

Glossary of terms

ShopifyQL syntax

ShopifyQL keywords

FROM and SHOW

WHERE

Comparison operators

Logical operators

GROUP BY

Time Dimensions

TIMESERIES

TIMESERIES and the order of columns

HAVING

WITH

TOTALS

GROUP_TOTALS

PERCENT_CHANGE

SINCE and UNTIL

Offset operators

Date functions

DURING

Named date range operators

COMPARE TO

Relative date range operators

ORDER BY

TIMESERIES and the order of columns

LIMIT

VISUALIZE and TYPE

AS

Other ShopifyQL functions and operators

Mathematical operators

Functions

Implicit joins

Partial string and array matching

Comments