I tried to extract a value from the key-values in Bigquery.

The input is:{"from":"bookmark","video_category":6,"video_id":22719,"content_ownership":"owner"}, then I use regex101 to construct the regex, so I get this r'(?:video_id":)(.*?)(?=\,|$)'

RegEx input in regex101

Then, I tried to implement it on Bigquery but it raised "Cannot parse regular expression: invalid perl operator: (?="

RegEx implementation in Bigquery

What is the issue here?

CodePudding user response：

If your input data is JSON, then you can try json_extract_scalar function:

with cte as
(
  select '{"from":"bookmark","video_category":6,"video_id":22719,"content_ownership":"owner"}' as data
)
select  json_extract_scalar(t.data, "$.video_id") as video_id
from    cte t

Regarding to REGEXP_EXTRACT. BigQuery docs says:

Note: BigQuery provides regular expression support using the re2 library; see that documentation for its regular expression syntax.

RE2 library docs says:

(?=re) before text matching re (NOT SUPPORTED)