REGEXP_EXTRACT_ALL extract "search operators" in a query

Question

I'm trying to use BigQuery's REGEXP_EXTRACT_ALL function to parse "search queries" of the form operator: query. There can be multiple operator/query pairs in a query string, and the "subquery" that falls under an operator can have multiple words enclosed by parens. I'd like to extract only the "search operators" from the query string using a regular expression.

For example:

animal: cat -> ["animal"]
animal: (mountain lion) -> ["animal"]
month: february day: sunday -> ["month", "day"]
date: (july 31) weather: (blue sky) -> ["date", "weather"]

Any suggestions? I'm definitely a regex noob, the best I've done so far is

REGEXP_EXTRACT_ALL(query, "(.\*):") AS operator

which recursively expands all of the text seen before colons (giving me results like ["date", "date: (july 31) weather"]).

One last "advanced" feature I'd like to implement is only including search operators if they are fully made up of text. For example:
time: 2020-07-08T17:09:14.043Z -> ["time"]

Thanks!

user_11329890 · Answer

Use

regexp_extract_all(query, r'\b([a-zA-Z]+):') AS operator

See proof.

Explanation

--------------------------------------------------------------------------------
  \b                       the boundary between a word char (\w) and
                           something that is not a word char
--------------------------------------------------------------------------------
  (                        group and capture to \1:
--------------------------------------------------------------------------------
    [a-zA-Z]+                any character of: 'a' to 'z', 'A' to 'Z'
                             (1 or more times (matching the most
                             amount possible))
--------------------------------------------------------------------------------
  )                        end of \1
--------------------------------------------------------------------------------
  :                        ':'

user_5221944 · Answer

Try below

regexp_extract_all(query, r'([^: ]+):') AS operator

if to apply to sample data in your question - output is

REGEXP_EXTRACT_ALL extract "search operators" in a query

2 Answers