Sql snobuilding 一个表，其中包含snowflake中键值数组中的动态列

发布于07月27日

我有下表-

ID , DATA
1   [{"key":"Apple", "value":2}, {"key":"Orange", "value":3}]
2   [{"key":"Apple", "value":5}, {"key":"Orange", "value":4}, {"key":"Cookie", "value":4}]

我想建立下表:

Id, Apple, Orange, Cookie
1   2      3     
2   5      4      4

我try 了parse_json和flatten的多种组合，但似乎都不支持这种 struct .

推荐答案

样本数据:

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}]');

步骤1-解析:

SELECT id, s.VALUE:key::TEXT AS key, s.VALUE:value::TEXT AS value
FROM tab
,LATERAL FLATTEN(input=>tab.DATA) s;

输出:

第2步:枢轴

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;

输出: