building a table with dynamic columns from a key value array in snowflake

Question

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.

Answer 1

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: