Four missing Functions in BigQuery

And how to work around them

Photo by Ingmar H on Unsplash

I really enjoy working with Google BigQuery but compared to other SQL dialects like PostgreSQL, MySQL, or SQL Server, BigQuery lacks some commonly used functions. In this article, we’ll explore four SQL functions that BigQuery does not support natively and provide alternative ways to achieve the same results.

Lets start with

STRING_AGG() with ORDER BY

Many SQL databases allow the STRING_AGG() function to concatenate string values ​​with an option to order them beforehand[1]. For example, in PostgreSQL:

SELECT STRING_AGG(name, ‘, ‘ ORDER BY name) AS concatenated_names FROM employees;

BigQuery supports STRING_AGG(), but it does not allow the ORDER BY clause inside the function.

Workaround in BigQuery: To get around this limitation, you can use ARRAY_AGG() with ORDER BY, then convert it into a string:

SELECT STRING_AGG(name, ‘, ‘)
FROM (
 SELECT name FROM employees ORDER BY name
);

This approach ensures that names are concatenated in the correct order.

LAG() and LEAD() with IGNORE NULLS