JavaScript Functions in BigQuery

• 1 minute

BigQuery’s user-defined functions (UDFs) provide a way to write custom JavaScript functions in SQL queries.

https://cloud.google.com/bigquery/docs/user-defined-functions#javascript-udf-structure

Pro Tip: Use JSON Type + Prefilter Data

Shoutout to Barry Pollard for showing me how to use JSON type parameters and prefilter my data. Simple optimizations like this cut data processing by over 75% for several queries in the Web Almanac project, saving hundreds of dollars in query costs.

For reference, see my Performance 2025 Queries pull request and look for “optimize” commits.

- CREATE TEMPORARY FUNCTION getUnloadHandler(audit STRING)
+ CREATE TEMPORARY FUNCTION getUnloadHandler(items JSON)
  RETURNS BOOL LANGUAGE js AS '''
  try {
-   var $ = JSON.parse(audit);
-   return $.details?.items?.some(n => n.value?.toLowerCase()?.includes("unload event listeners"));
+   return items?.some(n => n.value?.toLowerCase()?.includes("unload event listeners"));
  } catch (e) {
    return false;
  }
  ''';
  
  SELECT
    client,
    page,
    rank,
-   getUnloadHandler(TO_JSON_STRING(lighthouse.audits.deprecations)) AS has_unload
+   getUnloadHandler(lighthouse.audits.deprecations.details.items) AS has_unload
  FROM
    `httparchive.crawl.pages`
  WHERE
    date = "2025-07-01"