Here is my schema
I would like to flatten this into one row, so that all the structs in the prices array turn into columns so that column name would be combined of "market" + " " + "wait", and the columns field value would be "price". Is this possible?
Consier below query. You need a dynamic query cause you don't know what value is in market and wait field in advance and a column name with a space is not allowed in bigquery.
CREATE TEMP TABLE sample_table AS
SELECT 1 id, 'token001' token,
[STRUCT('market10' AS market, 100 AS wait, 100 AS price),
STRUCT('market11' AS market, 101 AS wait, 101 AS price)] prices, 100 result
UNION ALL
SELECT 2 id, 'token002' token,
[STRUCT('market20' AS market, 200 AS wait, 200 AS price),
STRUCT('market21' AS market, 201 AS wait, 201 AS price)] prices, 200 result;
EXECUTE IMMEDIATE FORMAT("""
SELECT * FROM (
SELECT id, token, result, market || '_' || wait AS col_name, price
FROM sample_table, UNNEST(prices)
) PIVOT (ANY_VALUE(price) FOR col_name IN ('%s'))
""", (SELECT STRING_AGG(market || '_' || wait, "','") FROM sample_table, UNNEST(prices)));
+----+----+----------+--------+--------------+--------------+--------------+--------------+
| ow | id | token | result | market10_100 | market11_101 | market20_200 | market21_201 |
+----+----+----------+--------+--------------+--------------+--------------+--------------+
| 1 | 1 | token001 | 100 | 100 | 101 | null | null |
| 2 | 2 | token002 | 200 | null | null | 200 | 201 |
+----+----+----------+--------+--------------+--------------+--------------+--------------+
