Menu
In BigQuery I have done a REGEXP_EXTRACT_ALL query and have results where each row contains multiple lines (an array).
How can query this table to convert each row's array into its own row so that my resulting table is individual rows of each line, instead of an array?
772
To flatten an entire column of ARRAY s while preserving the values of the other columns in each row, use a correlated cross join to join the table containing the ARRAY column to the UNNEST output of that ARRAY column.
You can do this with a CROSS JOIN. A cross join will take every individual element of your unnested array and join it back to its parent row. This will create multiple rows for each element of your array but you can then filter it down.
ARRAY_AGG. Returns an ARRAY of expression values. To learn more about the optional arguments in this function and how to use them, see Aggregate function calls. To learn more about the OVER clause and how to use it, see Window function calls.
You can apply the unnest operation. As an example:
WITH data AS(
SELECT ['string1', 'string2'] AS r UNION ALL
SELECT ['string3', 'string4', 'string5']
)
SELECT
r
FROM data,
UNNEST(r) r
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
