BigQuery SQL Formatter
Paste a BigQuery (GoogleSQL) query and get it formatted instantly. Backtick-quoted `project.dataset.table` paths, STRUCT and ARRAY expressions and @named query parameters are all handled.
A BigQuery query, before and after
The unformatted query below is the sample loaded into the editor above, and the output is exactly what this formatter returns for it with the default options. Nothing was tidied up by hand.
WITH sessions AS (SELECT user_id, session_id, TIMESTAMP_TRUNC(event_ts, HOUR) AS hr, ARRAY_AGG(event_name ORDER BY event_ts) AS events FROM `my-project.analytics.events` WHERE event_ts BETWEEN @start_ts AND @end_ts AND platform = @platform GROUP BY user_id, session_id, hr) SELECT hr, count(DISTINCT user_id) AS users, APPROX_QUANTILES(ARRAY_LENGTH(events), 100)[OFFSET(95)] AS p95_events FROM sessions WHERE ARRAY_LENGTH(events) > @min_events GROUP BY hr ORDER BY hrWITH
sessions AS (
SELECT
user_id,
session_id,
TIMESTAMP_TRUNC(event_ts, HOUR) AS hr,
ARRAY_AGG(
event_name
ORDER BY
event_ts
) AS events
FROM
`my-project.analytics.events`
WHERE
event_ts BETWEEN @start_ts AND @end_ts
AND platform = @platform
GROUP BY
user_id,
session_id,
hr
)
SELECT
hr,
count(DISTINCT user_id) AS users,
APPROX_QUANTILES(ARRAY_LENGTH(events), 100) [OFFSET(95)] AS p95_events
FROM
sessions
WHERE
ARRAY_LENGTH(events) > @min_events
GROUP BY
hr
ORDER BY
hrBigQuery syntax it handles
A generic formatter mangles dialect-specific syntax or refuses it outright. These are the BigQuery constructs this formatter is tested against.
@name BigQuery named query parameters are detected and grouped, so a parameter reused across CTEs is filled once.
`project.dataset.table` Fully-qualified backtick-quoted table paths stay on one piece.
UNNEST(array) Array flattening keeps its clause structure.
arr[OFFSET(0)] OFFSET and ORDINAL array subscripts are preserved verbatim.
STRUCT<...> Struct type declarations are not broken up.
BigQuery formatter FAQ
Does it detect @named parameters?
Yes. BigQuery's @name parameters are listed in the Parameters panel, grouped by name, and can be filled in to produce a runnable query.
Will it mangle my backtick table paths?
No. `project.dataset.table` is treated as a single quoted identifier and passes through unchanged.
Does it handle UNNEST, STRUCT and ARRAY expressions?
Yes. UNNEST keeps its clause structure instead of being reflowed as an ordinary function call, STRUCT type declarations are not broken apart mid-declaration, and ARRAY_AGG with an inner ORDER BY keeps that ordering attached to the aggregate it belongs to.
Will array subscripts such as OFFSET and ORDINAL survive formatting?
Yes, verbatim. A chained expression like APPROX_QUANTILES(x, 100)[OFFSET(95)] comes back exactly as written rather than being split at the bracket, which is what turns a working percentile query into a syntax error.
Can I edit the WHERE clause of one CTE in a long GoogleSQL query?
Yes. Each WITH block's WHERE is listed separately under that block's name, so you can open the one you care about, edit its conditions as a tree or a graph, and leave every other block untouched.
Does it work with BigQuery Legacy SQL?
No — this page targets GoogleSQL, which is BigQuery's standard dialect and what new queries should be written in. Legacy SQL's bracketed table references are not parsed. If you still have Legacy SQL, migrating it is a better use of your time than formatting it.