如何编写SQL查询将JSON类型键值对转为列展示？

有一个存储销售数据的数据库表，关联的额外费用以键值对格式存储在json类型字段中，表的最简结构如下：

saleID  amount extra_charges
------- ------ ----------------------------------------------------------
123     1000   [{"key": "Handling Charges", "amount": 20}, {"key": "Packing Charges", "amount": 15}]
345     1500   [{"key": "Transportation Charges", "amount": 10}, {"key": "Packing Charges", "amount": 0}]
567     240    [{"key": "Handling Charges", "amount": 10}, {"key": "Transportation Charges", "amount": 20}, {"key": "Packing Charges", "amount": 15}]
...

需要将数据在仪表盘中展示为以下格式：

Sale ID  Amount  Handling Charges   Transportation Charges   Packing Charges
-------  ------  -----------------  -----------------------  ----------------
123      1000    20                 0                        15
345      1500    0                  10                       0
567      240     10                 20                       15
...

尝试使用json_extract(extra_charges, '$.key')提取数据但失败，需要编写正确的SQL查询语句。

你之前的查询失败是因为extra_charges是JSON数组而非单个JSON对象，直接用$.key无法定位到数组内的元素。需要先将JSON数组展开为行，再通过条件聚合将不同费用类型转为列。以下是主流数据库的实现方式：

MySQL 8.0+ #

使用JSON_TABLE函数将JSON数组拆分为行，再用MAX(CASE...)进行列转行：

SELECT
    s.saleID AS `Sale ID`,
    s.amount AS Amount,
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Handling Charges' THEN ec.charge_amount END), 0) AS `Handling Charges`,
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Transportation Charges' THEN ec.charge_amount END), 0) AS `Transportation Charges`,
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Packing Charges' THEN ec.charge_amount END), 0) AS `Packing Charges`
FROM
    sales s
LEFT JOIN
    JSON_TABLE(
        s.extra_charges,
        '$[*]' COLUMNS(
            charge_key VARCHAR(50) PATH '$.key',
            charge_amount INT PATH '$.amount'
        )
    ) ec ON 1=1
GROUP BY
    s.saleID, s.amount;

PostgreSQL #

利用jsonb_to_recordset（若字段为json类型则用json_to_recordset）展开数组，再通过条件聚合实现列转行：

SELECT
    s.saleID AS "Sale ID",
    s.amount AS Amount,
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Handling Charges' THEN ec.charge_amount END), 0) AS "Handling Charges",
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Transportation Charges' THEN ec.charge_amount END), 0) AS "Transportation Charges",
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Packing Charges' THEN ec.charge_amount END), 0) AS "Packing Charges"
FROM
    sales s
LEFT JOIN
    jsonb_to_recordset(s.extra_charges::jsonb) AS ec(charge_key text, charge_amount int) ON true
GROUP BY
    s.saleID, s.amount;

SQL Server #

使用OPENJSON解析JSON数组，再进行条件聚合：

SELECT
    s.saleID AS [Sale ID],
    s.amount AS Amount,
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Handling Charges' THEN ec.charge_amount END), 0) AS [Handling Charges],
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Transportation Charges' THEN ec.charge_amount END), 0) AS [Transportation Charges],
    COALESCE(MAX(CASE WHEN ec.charge_key = 'Packing Charges' THEN ec.charge_amount END), 0) AS [Packing Charges]
FROM
    sales s
LEFT JOIN
    OPENJSON(s.extra_charges)
    WITH (
        charge_key VARCHAR(50) '$.key',
        charge_amount INT '$.amount'
    ) ec ON 1=1
GROUP BY
    s.saleID, s.amount;

说明 #

• COALESCE(..., 0)用于将未匹配到的费用类型值转为0，符合展示需求；
• 若你的数据库版本不支持上述JSON展开函数，可考虑用字符串处理函数拆分JSON数组，但效率较低，优先推荐使用原生JSON函数。

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