如何在PostgreSQL中利用两张表生成指定格式的JSON

以下以PostgreSQL为例（它对JSON的原生支持非常友好，适合初学者快速实现需求），给出完整实现方案，同时补充MySQL的适配版本。

完整PostgreSQL实现代码 #

WITH unpivoted_data AS (
    -- 把宽表转为长表，拆分年份列
    SELECT
        city,
        'val_2020' AS label,
        val_2020 AS value
    FROM data_tb
    UNION ALL
    SELECT
        city,
        'val_2021' AS label,
        val_2021 AS value
    FROM data_tb
),
chart_data_groups AS (
    -- 按年份分组，生成每个年份的chartData数组
    SELECT
        label,
        json_agg(json_build_object('key', city, 'value', value)) AS chartData
    FROM unpivoted_data
    GROUP BY label
),
unit_info AS (
    -- 处理单位和范围，把字符串转成数字数组
    SELECT
        unit,
        string_to_array(ranges, ', ')::numeric[] AS ranges
    FROM unit_tb
)
-- 最终组合成目标JSON
SELECT
    json_build_object(
        'data', json_agg(json_build_object('label', label, 'chartData', chartData)),
        'unit', (SELECT unit FROM unit_info),
        'ranges', (SELECT ranges FROM unit_info)
    ) AS result_json
FROM chart_data_groups;

分步解释（适合初学者理解） #

• 第一步：宽表转长表（Unpivot）
  原data_tb是宽表结构（每个年份单独成列），我们用UNION ALL把两个年份的数据合并，拆成「城市-年份标签-数值」的纵向结构，方便后续分组聚合。
• 第二步：生成年份对应的chartData数组
  按label（年份）分组，用json_agg把同一年份的城市数据聚合成JSON数组，json_build_object用来构造每个key-value格式的城市数据对象。
• 第三步：处理单位和范围数据
  把unit_tb中存储为字符串的ranges，用string_to_array拆分成数组，再转为数值类型，确保最终JSON里的范围是数字数组而非字符串。
• 第四步：组合最终JSON结构
  用json_build_object把data数组、unit、ranges三个部分拼接成目标格式的JSON，其中data部分是通过json_agg把年份对应的JSON对象聚合而成。

MySQL适配版本 #

如果使用MySQL，语法会有差异，实现代码如下：

WITH unpivoted_data AS (
    SELECT city, 'val_2020' AS label, val_2020 AS value FROM data_tb
    UNION ALL
    SELECT city, 'val_2021' AS label, val_2021 AS value FROM data_tb
),
chart_data_groups AS (
    SELECT
        label,
        JSON_ARRAYAGG(JSON_OBJECT('key', city, 'value', value)) AS chartData
    FROM unpivoted_data
    GROUP BY label
),
unit_info AS (
    SELECT
        unit,
        JSON_ARRAYAGG(CAST(TRIM(val) AS UNSIGNED)) AS ranges
    FROM unit_tb,
         JSON_TABLE(
             CONCAT('[', ranges, ']'),
             '$[*]' COLUMNS(val VARCHAR(10) PATH '$')
         ) AS jt
    GROUP BY unit
)
SELECT
    JSON_OBJECT(
        'data', (SELECT JSON_ARRAYAGG(JSON_OBJECT('label', label, 'chartData', chartData)) FROM chart_data_groups),
        'unit', (SELECT unit FROM unit_info),
        'ranges', (SELECT ranges FROM unit_info)
    ) AS result_json
FROM dual;

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