Скачать или смотреть Unlocking the Power of UNNEST with REGEXP_EXTRACT_ALL in BigQuery

Learn how to successfully use `UNNEST` in BigQuery to extract all items from an array generated by `REGEXP_EXTRACT_ALL`. Transform your data extraction process today!
---
This video is based on the question https://stackoverflow.com/q/62985235/ asked by the user 'Praxiteles' ( https://stackoverflow.com/u/1174302/ ) and on the answer https://stackoverflow.com/a/62985527/ provided by the user 'Mikhail Berlyant' ( https://stackoverflow.com/u/5221944/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to UNNEST a REGEXP_EXTRACT_ALL in BigQuery?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to UNNEST a REGEXP_EXTRACT_ALL in BigQuery

If you're working with BigQuery and need to extract data from a blob of text to have each item on its own row, you may have come across some challenges. A common issue is that while using REGEXP_EXTRACT_ALL, you may find that your query only extracts the first item instead of all items in the array. Let's walk through this problem and explore how to effectively use UNNEST in combination with REGEXP_EXTRACT_ALL to get your desired results.

The Problem

Suppose you have a block of text that lists different antibiotics, separated by double spaces, as shown below:

[[See Video to Reveal this Text or Code Snippet]]

You want to extract each antibiotic name and have them listed on separate rows in your result set. However, using UNNEST along with REGEXP_EXTRACT_ALL can sometimes lead to confusion and not return all expected values.

In your initial attempt, the query you wrote looked something like this:

[[See Video to Reveal this Text or Code Snippet]]

This query gives a structured output but may not yield all of the antibiotics separated into individual rows.

The Solution

Here’s where the adjustment needs to be made: Instead of selecting everything with SELECT *, you should directly select the unnested values. This simple change will allow you to effectively list each antibiotic on its own row.

Revised Query

The revised query should look like this:

[[See Video to Reveal this Text or Code Snippet]]

Breakdown of the Fix

Here’s what happens in the revised query:

REGEXP_EXTRACT_ALL: This function processes the blob and extracts all the antibiotic names in the array based on the specified pattern.

UNNEST: By using UNNEST(antibiotic) AS unnested_antibiotic, you flatten the array, making each antibiotic appear on its own row.

Direct Selection: The change from SELECT * to SELECT unnested_antibiotic focuses the results to just the extracted value without unnecessary joins or additional columns.

Conclusion

With this adjustment, your query now effectively extracts each antibiotic into separate rows. Using UNNEST properly can transform a complex string extraction into a clean, organized dataset.

If you encounter similar issues in your data processing tasks, remember that subtle changes in your SQL queries can lead to drastically different results. Happy querying in BigQuery!