Extract query parameters from a URL as ARRAY in BigQuery

bigquery-extract-url-parameters-array.sql

#standardSQL
# Extract query parameters from a URL as ARRAY in BigQuery; standard-sql; 2018-04-08
# @see http://www.pascallandau.com/bigquery-snippets/extract-url-parameters-array/
WITH examples AS (
  SELECT 1   AS id, 
  '?foo=bar' AS query, 
  'simple'   AS description
  UNION ALL SELECT 2, '?foo=bar&bar=baz', 'multiple params'
  UNION ALL SELECT 3, '?foo[]=bar&foo[]=baz', 'arrays'
  UNION ALL SELECT 4, '', 'no query'
)
SELECT 
  id, 
  query,
  REGEXP_EXTRACT_ALL(query,r'(?:\?|&)((?:[^=]+)=(?:[^&]*))') as params,
  REGEXP_EXTRACT_ALL(query,r'(?:\?|&)(?:([^=]+)=(?:[^&]*))') as keys,
  REGEXP_EXTRACT_ALL(query,r'(?:\?|&)(?:(?:[^=]+)=([^&]*))') as values,
  description
FROM examples