如何用PostgreSQL将JSON列拆分为问题与答案两列？

原始数据结构 #

目标结构 #

下面按常用数据库给出具体实现：

PostgreSQL 实现 #

利用jsonb_array_elements展开JSON数组，再用jsonb_each拆分键值对：

SELECT 
    t.data,
    kv.key AS question,
    kv.value AS answer
FROM 
    your_table t,
    jsonb_array_elements(t.json::jsonb) AS arr,
    jsonb_each(arr) AS kv;

如果你的JSON列是json类型，把jsonb替换成json即可。

MySQL 8.0+ 实现 #

通过JSON_TABLE提取所有问题键名，再匹配对应答案：

SELECT 
    t.data,
    j.question,
    JSON_UNQUOTE(JSON_EXTRACT(t.json, CONCAT('$[0].', j.question))) AS answer
FROM 
    your_table t,
    JSON_TABLE(
        JSON_KEYS(JSON_EXTRACT(t.json, '$[0]')),
        '$[*]' COLUMNS (question VARCHAR(50) PATH '$')
    ) j;

注：示例假设JSON数组内仅含一个对象，若有多个对象需调整路径逻辑。

SQL Server 实现 #

先通过OPENJSON解析JSON，再用UNPIVOT转成行记录：

SELECT 
    t.data,
    unpvt.question,
    unpvt.answer
FROM 
    your_table t
CROSS APPLY 
    OPENJSON(t.json)
    WITH (
        Q1 VARCHAR(50) '$.Q1',
        Q2 VARCHAR(50) '$.Q2',
        Q3 VARCHAR(50) '$.Q3'
    ) AS j
UNPIVOT (
    answer FOR question IN (Q1, Q2, Q3)
) AS unpvt;

如果问题数量不固定，可使用动态SQL自动适配：

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX);

SELECT @cols = STRING_AGG(QUOTENAME([key]), ',')
FROM your_table t
CROSS APPLY OPENJSON(t.json)
CROSS APPLY OPENJSON(JSON_VALUE(t.json, '$[0]'));

SET @query = N'
SELECT 
    t.data,
    unpvt.question,
    unpvt.answer
FROM 
    your_table t
CROSS APPLY 
    OPENJSON(t.json)
    WITH (' + @cols + ') AS j
UNPIVOT (
    answer FOR question IN (' + @cols + ')
) AS unpvt;';

EXEC sp_executesql @query;

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