CODEWITHSUNDEEP
regexgoogle-bigquery
Praxiteles
Praxiteles

Reputation: 6030

How to UNNEST a REGEXP_EXTRACT_ALL in BigQuery?

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?

Upvotes: 2

Views: 1767

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

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

Upvotes: 1

Related Questions