Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@paslandau

paslandau/bigquery-extract-url-parameters-array.sql

Last active May 16, 2023 06:09
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save paslandau/6c46020211a00c39607d5eab1d093f3a to your computer and use it in GitHub Desktop.
Save paslandau/6c46020211a00c39607d5eab1d093f3a to your computer and use it in GitHub Desktop.
Download ZIP
Extract query parameters from a URL as ARRAY in BigQuery
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment