PostgreSQL中按item_code分组聚合JSON数组字段求和

针对存储在数据库中的多层JSON数组数据，要分别提取bars和coils下的条目，按item_code分组求和inventory_quantity_kg和inventory_pieces_nets，以下是主流关系型数据库的实现方案：

PostgreSQL 实现（支持JSONB类型） #

假设数据表名为inventory_data，存储JSON的字段为json_col（推荐用jsonb类型以获得更好的性能）。

单独统计bars/coils #

-- 统计bars分组求和
SELECT
    bar->>'item_code' AS item_code,
    SUM((bar->>'inventory_quantity_kg')::numeric) AS bars_total_kg,
    SUM((bar->>'inventory_pieces_nets')::integer) AS bars_total_pieces
FROM
    inventory_data,
    jsonb_array_elements(json_col) AS top_obj, -- 展开顶层JSON数组
    jsonb_array_elements(top_obj->'bars') AS bar -- 展开每个顶层对象下的bars数组
GROUP BY
    item_code;

-- 统计coils分组求和
SELECT
    coil->>'item_code' AS item_code,
    SUM((coil->>'inventory_quantity_kg')::numeric) AS coils_total_kg,
    SUM((coil->>'inventory_pieces_nets')::integer) AS coils_total_pieces
FROM
    inventory_data,
    jsonb_array_elements(json_col) AS top_obj,
    jsonb_array_elements(top_obj->'coils') AS coil
GROUP BY
    item_code;

合并bars和coils的统计结果 #

用FULL JOIN将两个统计结果合并，确保所有item_code都被展示：

WITH bars_stats AS (
    SELECT
        bar->>'item_code' AS item_code,
        SUM((bar->>'inventory_quantity_kg')::numeric) AS bars_total_kg,
        SUM((bar->>'inventory_pieces_nets')::integer) AS bars_total_pieces
    FROM
        inventory_data,
        jsonb_array_elements(json_col) AS top_obj,
        jsonb_array_elements(top_obj->'bars') AS bar
    GROUP BY
        item_code
),
coils_stats AS (
    SELECT
        coil->>'item_code' AS item_code,
        SUM((coil->>'inventory_quantity_kg')::numeric) AS coils_total_kg,
        SUM((coil->>'inventory_pieces_nets')::integer) AS coils_total_pieces
    FROM
        inventory_data,
        jsonb_array_elements(json_col) AS top_obj,
        jsonb_array_elements(top_obj->'coils') AS coil
    GROUP BY
        item_code
)
SELECT
    COALESCE(b.item_code, c.item_code) AS item_code,
    COALESCE(b.bars_total_kg, 0) AS bars_total_kg,
    COALESCE(b.bars_total_pieces, 0) AS bars_total_pieces,
    COALESCE(c.coils_total_kg, 0) AS coils_total_kg,
    COALESCE(c.coils_total_pieces, 0) AS coils_total_pieces
FROM
    bars_stats b
FULL JOIN
    coils_stats c ON b.item_code = c.item_code
ORDER BY
    item_code;

MySQL 实现（8.0+版本，支持JSON_TABLE） #

同样假设表名为inventory_data，JSON字段为json_col。

单独统计bars/coils #

-- 统计bars分组求和
SELECT
    j.item_code,
    SUM(j.inventory_quantity_kg) AS bars_total_kg,
    SUM(j.inventory_pieces_nets) AS bars_total_pieces
FROM
    inventory_data,
    JSON_TABLE(
        json_col,
        '$[*].bars[*]' COLUMNS ( -- 直接定位到bars数组的每个元素
            item_code VARCHAR(255) PATH '$.item_code',
            inventory_quantity_kg DECIMAL(10,4) PATH '$.inventory_quantity_kg',
            inventory_pieces_nets INT PATH '$.inventory_pieces_nets'
        )
    ) AS j
GROUP BY
    j.item_code;

-- 统计coils分组求和
SELECT
    j.item_code,
    SUM(j.inventory_quantity_kg) AS coils_total_kg,
    SUM(j.inventory_pieces_nets) AS coils_total_pieces
FROM
    inventory_data,
    JSON_TABLE(
        json_col,
        '$[*].coils[*]' COLUMNS (
            item_code VARCHAR(255) PATH '$.item_code',
            inventory_quantity_kg DECIMAL(10,4) PATH '$.inventory_quantity_kg',
            inventory_pieces_nets INT PATH '$.inventory_pieces_nets'
        )
    ) AS j
GROUP BY
    j.item_code;

合并bars和coils的统计结果 #

WITH bars_stats AS (
    SELECT
        j.item_code,
        SUM(j.inventory_quantity_kg) AS bars_total_kg,
        SUM(j.inventory_pieces_nets) AS bars_total_pieces
    FROM
        inventory_data,
        JSON_TABLE(
            json_col,
            '$[*].bars[*]' COLUMNS (
                item_code VARCHAR(255) PATH '$.item_code',
                inventory_quantity_kg DECIMAL(10,4) PATH '$.inventory_quantity_kg',
                inventory_pieces_nets INT PATH '$.inventory_pieces_nets'
            )
        ) AS j
    GROUP BY
        j.item_code
),
coils_stats AS (
    SELECT
        j.item_code,
        SUM(j.inventory_quantity_kg) AS coils_total_kg,
        SUM(j.inventory_pieces_nets) AS coils_total_pieces
    FROM
        inventory_data,
        JSON_TABLE(
            json_col,
            '$[*].coils[*]' COLUMNS (
                item_code VARCHAR(255) PATH '$.item_code',
                inventory_quantity_kg DECIMAL(10,4) PATH '$.inventory_quantity_kg',
                inventory_pieces_nets INT PATH '$.inventory_pieces_nets'
            )
        ) AS j
    GROUP BY
        j.item_code
)
SELECT
    COALESCE(b.item_code, c.item_code) AS item_code,
    COALESCE(b.bars_total_kg, 0) AS bars_total_kg,
    COALESCE(b.bars_total_pieces, 0) AS bars_total_pieces,
    COALESCE(c.coils_total_kg, 0) AS coils_total_kg,
    COALESCE(c.coils_total_pieces, 0) AS coils_total_pieces
FROM
    bars_stats b
FULL JOIN
    coils_stats c ON b.item_code = c.item_code
ORDER BY
    item_code;

结果示例 #

基于你提供的JSON数据，合并后的统计结果如下：

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