Extract from string in BigQuery using regexp_extract

Question

I have a long string in BigQuery where that I need to extract out some data.

Part of the string looks like this:

... source: "agent" resolved_query: "hi" score: 0.61254 parameters ...

I want to extract out data such as agent, hi, and 0.61254.

I'm trying to use regexp_extract but I can't get the regexp to work correctly:

select
regexp_extract([col],r'score: [0-9]*\.[0-9]+') as score,
regexp_extract([col],r'source: [^"]*') as source
from [table]

What should the regexp be to just get agent or 0.61254 without the field name and no quotation marks?

Thank you in advance.

Wiktor Stribiżew · Accepted Answer · 2021-12-14 22:35:26Z

5

You can use

select
regexp_extract([col],r'score:\s*(\d*\.?\d+)') as score,
regexp_extract([col],r'resolved_query:\s*"([^"]*)"') as resolved_query,
regexp_extract([col],r'source:\s*"([^"]*)"') as source
from [table]

Here,

score:\s*(\d*\.?\d+) matches score: string, then any zero or more whitespaces, and then there is a capturing group with ID=1 that captures zero or more digits, an optional . and then one or more digits
resolved_query:\s*"([^"]*)" matches a resolved_query: string, zero or more whitespaces, ", then captures into Group 1 any zero or more chars other than " and then matches a " char
source:\s*"([^"]*)" matches a source: string, zero or more whitespaces, ", then captures into Group 1 any zero or more chars other than " and then matches a " char.

answered Dec 14, 2021 at 22:35

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jean Picard Over a year ago

Thank you. This worked perfectly. And I especially appreciate the explanation of what regex is doing - really helped me understand as it is not intuitive.

Mikhail Berlyant · Accepted Answer · 2021-12-14 22:48:46Z

I love non-trivial approaches - below one of such -

select * except(col) from (
  select col, split(kv, ': ')[offset(0)] key,
    trim(split(kv, ': ')[offset(1)], '"') value,
  from your_table,
  unnest(regexp_extract_all(col, r'\w+: "?[\w.]+"?')) kv
)
pivot (min(value) for key in ('source', 'resolved_query', 'score'))

if applied to sample data as in your question

with your_table as (
  select '...  source: "agent"  resolved_query: "hi"  score: 0.61254  parameters ... ' col union all
  select '...  source: "agent2"  resolved_query: "hello"  score: 0.12345  parameters ... ' col 
)

the output is

As you might noticed, the benefit of such approach is obvious - if you have more fields/attributes to extract - you do not need to clone the lines of code for each of attribute - you just add yet another value in last line's list - the whole code is always the same

Collectives™ on Stack Overflow

Extract from string in BigQuery using regexp_extract

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related