Extract parameter from url in Google BigQuery using regex

2
votes

May I ask your help in order to build a regular expression to be used on Google Big Query using REGEXP_EXTRACT that will parse the value of an url parameter identified by a specific key?

Let's suppose that the parameter I would like to parse has key value equal to "source". The parsing should:

  • Ensure that before the key there is "?" or a "&" and after there is a "=" : so in the example match "?source=" or "&source="
  • Capture the value until the first "&" or end of string
  • In case above conditions matches more than one time it should take the value of first occurance

Here are some example of desired behaviour (they should all provide "google" as output):

  • www.google.com?source=google&medium=cpc --> output: google
  • www.google.com?source=google --> output: google
  • www.google.com?medium=cpc&source=google --> output: google
  • www.google.com?medium=cpc&source=google&keyword=foo --> output: google
  • www.google.com?medium=cpc&source=google&keyword=foo&source=bing --> output: google
  • www.google.it?medium=cpc?source=goo-gle --> output: goo-gle
  • www.google.it?medium=cpc?source=google?med=cpc&keyword=foo --> output: google?med=cpc

Thanks very much for any help!

2
regexgoogle-bigquery

2 Answers

6
votes

[?&]source=([^&]+)

The first captured group in the match will be the value of the "source" parameter.

  • [?&] Either ? or &
  • source= Literal text
  • ([^&]+) A captured group containing 1 or more characters that are not &
5
votes

If you need to extract all parameters from a URL, you can also use REGEXP_EXTRACT_ALL as follows:

REGEXP_EXTRACT_ALL(query,r'(?:\?|&)((?:[^=]+)=(?:[^&]*))') as params

(Posting here because this question ranks highly on Google for "bigquery parse url query string", but the chosen answer only works for one parameter that is already defined).

This will return the result as an array (see How to extract URL parameters as ARRAY in Google BigQuery):

BigQuery example