如何查询SQL Server含JSON列的表并展开多节点为行

原表结构 #

表名：my_table，简化结构如下：

id, the_json
1, {json json json}
2, {json}
3, {json json}

JSON列示例格式 #

注意：原示例存在语法错误，messages应为数组（用[]包裹），修正后的有效JSON格式如下：

{
    "valid": false,
    "messages": [
        {
            "message": "blah",
            "error": true,
            "detail": "something1"
        },
        {
            "message": "blah blah",
            "error": true,
            "detail": "something2"
        },
        {
            "message": "blah blah blah",
            "error": false,
            "detail": "something3"
        }
    ]
}

需求 #

编写SQL查询，将the_json列中messages数组的每个节点转换为单独一行，同时关联原表的id，最终得到如下结果：

id, message, error, detail

1, blah, true, something1
1, blah blah, true, something2
1, blah blah blah, false, something3

2, blah, true, something1

3, blah, true, something1
3, blah blah, true, something2

解决方案 #

根据不同数据库类型，提供对应的SQL语句：

PostgreSQL（字段类型为json或jsonb） #

使用json_array_elements函数展开JSON数组：

SELECT
    t.id,
    msg->>'message' AS message,
    (msg->>'error')::boolean AS error,
    msg->>'detail' AS detail
FROM my_table t
JOIN LATERAL json_array_elements(t.the_json->'messages') AS msg ON true;

MySQL #

使用JSON_TABLE函数解析JSON数组：

SELECT
    t.id,
    jt.message,
    jt.error,
    jt.detail
FROM my_table t
JOIN JSON_TABLE(
    t.the_json,
    '$.messages[*]' COLUMNS (
        message VARCHAR(255) PATH '$.message',
        error BOOLEAN PATH '$.error',
        detail VARCHAR(255) PATH '$.detail'
    )
) jt;

SQL Server #

使用OPENJSON结合CROSS APPLY解析JSON数组：

SELECT
    t.id,
    jt.message,
    jt.error,
    jt.detail
FROM my_table t
CROSS APPLY OPENJSON(t.the_json, '$.messages')
WITH (
    message VARCHAR(255) '$.message',
    error BIT '$.error',
    detail VARCHAR(255) '$.detail'
) jt;

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