在PrestoSQL中动态将JSON对象键转为列的查询方法

针对你这种未知JSON键、需要将JSON对象动态展开为表列的需求，不同数据库有对应的实用实现方式，以下是主流数据库的具体操作方法：

MySQL 实现 #

在MySQL中，需要结合JSON_KEYS获取所有键，再通过动态SQL拼接查询语句：

1. 先获取目标行的所有JSON键：

SELECT JSON_KEYS(Data) FROM mytable WHERE SensorID = 1;

执行后会返回类似["O3", "CO2", "PM10"]的键数组。

2. 通过存储过程自动生成并执行动态查询：

DELIMITER //
CREATE PROCEDURE dynamic_json_unpack()
BEGIN
    DECLARE json_keys TEXT;
    SELECT GROUP_CONCAT('Data->"$."', key_name, '" AS ', key_name) INTO json_keys
    FROM (
        SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(Data), CONCAT('$[', idx, ']'))) AS key_name
        FROM mytable
        CROSS JOIN (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2) AS indices
        WHERE SensorID = 1 AND idx < JSON_LENGTH(JSON_KEYS(Data))
    ) AS keys_list;

    SET @sql = CONCAT('SELECT SensorID, Name, ', json_keys, ' FROM mytable WHERE SensorID = 1');
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //
DELIMITER ;

-- 调用存储过程获取结果
CALL dynamic_json_unpack();

注意：如果JSON键的数量不固定，需要调整indices子查询的数值范围，或者改用递归方式生成索引。

PostgreSQL 实现 #

PostgreSQL可以借助jsonb_each_text拆分JSON键值对，再结合crosstab完成行转列：

1. 先确保安装tablefunc扩展（未安装则执行）：

CREATE EXTENSION IF NOT EXISTS tablefunc;

2. 用动态SQL实现自动解析：

DO $$
DECLARE
    keys TEXT;
    sql TEXT;
BEGIN
    -- 获取目标行的所有唯一JSON键
    SELECT string_agg(DISTINCT quote_ident(key), ', ') INTO keys
    FROM mytable, jsonb_each_text(Data::jsonb)
    WHERE SensorID = 1;

    -- 拼接交叉表查询语句
    sql := format(
        'SELECT * FROM crosstab(
            ''SELECT SensorID, Name, key, value FROM mytable, jsonb_each_text(Data::jsonb) WHERE SensorID = 1'',
            ''SELECT DISTINCT key FROM mytable, jsonb_each_text(Data::jsonb) WHERE SensorID = 1''
        ) AS ct(SensorID INT, Name TEXT, %s)',
        keys
    );

    EXECUTE sql;
END $$;

该方案会自动识别目标行的所有JSON键，将其转为对应的表列。

SQL Server 实现 #

SQL Server通过OPENJSON解析JSON，再结合动态PIVOT实现结构化输出：

DECLARE @keys NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 获取目标行的所有JSON键
SELECT @keys = STRING_AGG(QUOTENAME([key]), ', ')
FROM (
    SELECT DISTINCT [key]
    FROM mytable
    CROSS APPLY OPENJSON(Data)
    WHERE SensorID = 1
) AS keys_list;

-- 拼接PIVOT查询语句
SET @sql = N'
SELECT SensorID, Name, ' + @keys + '
FROM (
    SELECT SensorID, Name, [key], [value]
    FROM mytable
    CROSS APPLY OPENJSON(Data)
    WHERE SensorID = 1
) AS src
PIVOT (
    MAX([value])
    FOR [key] IN (' + @keys + ')
) AS pvt;';

EXEC sp_executesql @sql;

执行这段脚本后，会自动将JSON中的所有键转为表列，输出你需要的结构化结果。

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