Unpivot for Strings
2 removals
| Words removed | 2 |
| Total words | 35 |
| Words removed (%) | 5.71 |
12 lines
2 additions
| Words added | 2 |
| Total words | 35 |
| Words added (%) | 5.71 |
12 lines
CREATE TEMP FUNCTION unpivot(x ANY TYPE, col_regex STRING)
CREATE TEMP FUNCTION unpivot(x ANY TYPE, col_regex STRING)
AS ((
AS ((
# https://medium.com/@hoffa/how-to-unpivot-multiple-columns-into-tidy-pairs-with-sql-and-bigquery-d9d0e74ce675
# https://medium.com/@hoffa/how-to-unpivot-multiple-columns-into-tidy-pairs-with-sql-and-bigquery-d9d0e74ce675
SELECT
SELECT
ARRAY_AGG(STRUCT(
ARRAY_AGG(STRUCT(
REGEXP_EXTRACT(y, '[^"]*') AS key
REGEXP_EXTRACT(y, '[^"]*') AS key
, REGEXP_EXTRACT(y, r':([^"]*)\"?[,}\]]') AS value
, REGEXP_EXTRACT(y, r':\"?([^"]*)\"?[,}\]]') AS value
))
))
FROM UNNEST((
FROM UNNEST((
SELECT REGEXP_EXTRACT_ALL(json,col_regex||r'[^:]+:\"?[^"]+\"?') arr
SELECT REGEXP_EXTRACT_ALL(json,col_regex||r'[^:]+:\"?[^"]+\"?[,}\]]') arr
FROM (SELECT TO_JSON_STRING(x) json))) y
FROM (SELECT TO_JSON_STRING(x) json))) y
));
));