I have the following table -
ID , DATA
1 [{"key":"Apple", "value":2}, {"key":"Orange", "value":3}]
2 [{"key":"Apple", "value":5}, {"key":"Orange", "value":4}, {"key":"Cookie", "value":4}]
I'd like to build the following table :
Id, Apple, Orange, Cookie
1 2 3
2 5 4 4
Ive tried many combinations of parse_json and flatten but none seemed to support this structure.
Sample data:
CREATE OR REPLACE TABLE tab
AS
SELECT 1 ID, PARSE_JSON('[{"key":"Apple", "value":2}, {"key":"Orange", "value":3}]') AS DATA
UNION
SELECT 2, PARSE_JSON('[{"key":"Apple", "value":5}, {"key":"Orange", "value":4}, {"key":"Cookie", "value":4}]');
Step 1 - parse:
SELECT id, s.VALUE:key::TEXT AS key, s.VALUE:value::TEXT AS value
FROM tab
,LATERAL FLATTEN(input=>tab.DATA) s;
Output:
Step 2: Pivot
WITH cte AS (
SELECT id, s.VALUE:key::TEXT AS key, s.VALUE:value::TEXT AS value
FROM tab
,LATERAL FLATTEN(input=>tab.DATA) s
)
SELECT *
FROM cte
PIVOT(MAX(value) FOR KEY IN ('Apple', 'Orange', 'Cookie')) AS p;
Output:
