How to UNNEST a REGEXP_EXTRACT_ALL in BigQuery?

user_1174302 0
Viewed 662

We are attempting to get a multi-row/one-row-per-item extract from a blob of text in BigQuery.

We seem to be able to only extract the first item in the array using UNNEST - whereas we would like every item to be on its own row.

Here's the code that duplicates the problem.

SELECT * FROM
(
  SELECT 
    REGEXP_EXTRACT_ALL(blob, r"(.+)\s{2,}") AS antibiotic
  FROM
  (

    SELECT """
      Amoxicillin/Clavulanic Acid    I
      Ampicillin                     R
      Penicillin                     S
      """ 
    AS blob
  )  
), UNNEST(antibiotic) unnested_antibiotic

What are we missing?

google-bigquery regex
1 Answers

Just use SELECT unnested_antibiotic FROM ... instead of SELECT * FROM ... in most outer SELECT statement

user_52219440
Related