如何在SQL中对JSON导入的动态键数据实现类UNPIVOT转换？

场景说明 #

第一种常规JSON结构 #

这种结构可以直接导入SQL，生成扁平化的单行数据：

{
    "person": {
        "name": "Todd",
        "age": 10
    }, 
    "address": {
        "name": "18B Victory",
        "zip": 20019
    }
}

导入后结构：

person.name      person.age       address.name       address.zip
"Todd"           10               "18B Victory"      20019

第二种待转换的JSON结构 #

这种以对象ID为顶级键的结构，直接导入SQL会生成横向扩展的列，不符合需求：

{
    "X1AB": {
        "name": "Todd",
        "age": 10
    }, 
    "Y2AL": {
        "name": "Brad",
        "age": 20
    }
}

直接导入后的结构：

X1AB.name     X1AB.age        Y2AL.name       Y2AL.age
"Todd"        10              "Brad"          20

我们需要将其转换为如下纵向结构：

_prefix         name         age
X1AB            "Todd"        10
Y2AL            "Brad"        20

实现方案 #

可以实现，无需依赖PIVOT/UNPIVOT，利用各SQL数据库的JSON解析函数即可完成，以下是主流数据库的具体实现：

SQL Server #

使用OPENJSON函数解析JSON，它会返回顶级键值对，再提取内部字段：

DECLARE @json NVARCHAR(MAX) = N'{
    "X1AB": {
        "name": "Todd",
        "age": 10
    }, 
    "Y2AL": {
        "name": "Brad",
        "age": 20
    }
}'

SELECT 
    [key] AS _prefix,
    JSON_VALUE(value, '$.name') AS name,
    JSON_VALUE(value, '$.age') AS age
FROM OPENJSON(@json)

PostgreSQL #

使用json_each（或jsonb_each）展开顶级键值对，再提取内部属性：

WITH json_data AS (
    SELECT '{
        "X1AB": {
            "name": "Todd",
            "age": 10
        }, 
        "Y2AL": {
            "name": "Brad",
            "age": 20
        }
    }'::json AS data
)
SELECT
    key AS _prefix,
    (value->>'name') AS name,
    (value->>'age')::INT AS age
FROM json_data, json_each(data)

MySQL #

结合JSON_TABLE和JSON_KEYS提取顶级键，再通过路径解析内部字段：

SET @json = '{
    "X1AB": {
        "name": "Todd",
        "age": 10
    }, 
    "Y2AL": {
        "name": "Brad",
        "age": 20
    }
}';

SELECT
    jt._prefix,
    JSON_UNQUOTE(JSON_EXTRACT(@json, CONCAT('$."', jt._prefix, '".name'))) AS name,
    JSON_EXTRACT(@json, CONCAT('$."', jt._prefix, '".age')) AS age
FROM JSON_TABLE(
    JSON_KEYS(@json),
    '$[*]' COLUMNS (_prefix VARCHAR(10) PATH '$')
) AS jt;

核心思路 #

所有方案的核心逻辑一致：先将JSON的顶级键值对展开，把顶级键映射为_prefix列，再从每个键对应的JSON值中提取name、age等内部字段，最终得到纵向的目标数据结构。

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