如何在AWS Athena中无需指定所有参数解析JSON到表格？

当然可以不用手动逐个指定JSON键来解析列，针对不同的SQL数据库，有对应的动态解析方案，以下是主流引擎的实现方式：

PostgreSQL #

PostgreSQL可以通过jsonb_each_text将JSON字段拆分为键值对行，再结合crosstab函数转置为列：

-- 1. 先获取所有唯一的JSON键
WITH all_keys AS (
    SELECT DISTINCT jsonb_object_keys(value::jsonb) AS key
    FROM my_table
),
-- 2. 将JSON拆分为键值对行
key_value_rows AS (
    SELECT 
        event_name,
        key,
        value
    FROM my_table,
    LATERAL jsonb_each_text(value::jsonb)
)
-- 3. 转置为宽表
SELECT *
FROM crosstab(
    'SELECT event_name, key, value FROM key_value_rows ORDER BY 1,2',
    'SELECT key FROM all_keys ORDER BY 1'
) AS ct(event_name text, "link_id" text, "spec" text, "geo" text);

如果键的数量不确定，可以用动态SQL自动生成crosstab的列定义：

DO $$
DECLARE
    cols text;
BEGIN
    SELECT string_agg(DISTINCT quote_ident(key) || ' text', ', ')
    INTO cols
    FROM my_table,
    LATERAL jsonb_object_keys(value::jsonb);

    EXECUTE format('
        SELECT *
        FROM crosstab(
            ''SELECT event_name, key, value FROM (SELECT event_name, key, value FROM my_table, LATERAL jsonb_each_text(value::jsonb)) kv ORDER BY 1,2'',
            ''SELECT DISTINCT key FROM my_table, LATERAL jsonb_object_keys(value::jsonb) ORDER BY 1''
        ) AS ct(event_name text, %s)
    ', cols);
END $$;

BigQuery #

BigQuery可以通过UNNEST展开JSON键值对，再用PIVOT转置，结合动态SQL自动生成列：

-- 先获取所有唯一的JSON键
DECLARE keys ARRAY<STRING>;
SET keys = ARRAY(
    SELECT DISTINCT json_extract_scalar(kv, '$.key')
    FROM my_table,
    UNNEST(json_extract_array(json_query(value, '$.keys()'), '$')) kv
);

-- 动态生成PIVOT查询
EXECUTE IMMEDIATE format("""
    SELECT *
    FROM (
        SELECT 
            event_name,
            json_extract_scalar(kv, '$.key') AS key,
            json_extract_scalar(kv, '$.value') AS value
        FROM my_table,
        UNNEST(json_extract_array(json_query(value, '$.entries()'), '$')) kv
    )
    PIVOT (MAX(value) FOR key IN (%s))
""", array_to_string(ARRAY(SELECT CONCAT('"', key, '"') FROM UNNEST(keys)), ', '));

MySQL（8.0+） #

MySQL 8.0及以上版本可以用JSON_TABLE展开JSON，再通过动态SQL生成查询：

-- 先获取所有唯一的JSON键
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN `key` = ''', `key`, ''' THEN `value` END) AS `', `key`, ''''))
INTO @cols
FROM my_table,
JSON_TABLE(
    value,
    '$.*' COLUMNS (
        `key` VARCHAR(255) PATH '$."key"',
        `value` VARCHAR(255) PATH '$."value"'
    )
) AS jt;

-- 动态生成查询
SET @sql = CONCAT('
    SELECT event_name, ', @cols, '
    FROM my_table,
    JSON_TABLE(
        value,
        ''$.*'' COLUMNS (
            `key` VARCHAR(255) PATH ''$."key"'',
            `value` VARCHAR(255) PATH ''$."value"''
        )
    ) AS jt
    GROUP BY event_name
');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

以上方案都能自动识别JSON中的所有键，无需手动逐个指定，适配你近100个参数的场景。

内容的提问来源于stack exchange，提问作者Smasell