如何将含JSON数组字段的表与另一张表关联并丰富数据

原始表结构与数据 #

表1：market_baskets（市场购物篮表） #

表2：product_prices（商品价格表） #

目标结果表 #

解决方案 #

PostgreSQL版本（推荐，原生JSONB支持更高效） #

逻辑步骤：

1. 拆分JSON数组为单个商品条目
2. 关联价格表获取对应价格
3. 给每个商品条目添加price字段
4. 重新聚合为JSON数组，恢复原始行结构

WITH basket_items AS (
    -- 拆分JSON数组，提取每个商品条目和article_id
    SELECT
        mb."市场ID",
        mb.ctid AS row_id, -- 用ctid标识原始行，有主键则替换为主键
        jsonb_array_elements(mb."购物篮位置"::jsonb) AS item,
        (jsonb_array_elements(mb."购物篮位置"::jsonb)->>'article_id')::int AS article_id
    FROM market_baskets mb
),
items_with_price AS (
    -- 关联价格表，补充price字段
    SELECT
        bi."市场ID",
        bi.row_id,
        jsonb_set(bi.item, '{price}', to_jsonb(pp."价格")) AS item_with_price
    FROM basket_items bi
    LEFT JOIN product_prices pp ON bi.article_id = pp.article_id
)
-- 重新聚合为JSON数组
SELECT
    "市场ID",
    jsonb_agg(item_with_price) AS "购物篮位置"
FROM items_with_price
GROUP BY "市场ID", row_id
ORDER BY "市场ID", row_id;

MySQL版本参考 #

WITH basket_items AS (
    -- 拆分JSON数组
    SELECT
        mb.`市场ID`,
        mb.id AS row_id, -- 假设表有主键id
        jt.item,
        JSON_UNQUOTE(JSON_EXTRACT(jt.item, '$.article_id')) AS article_id
    FROM market_baskets mb
    JOIN JSON_TABLE(
        mb.`购物篮位置`,
        '$[*]' COLUMNS (item JSON PATH '$')
    ) jt
),
items_with_price AS (
    -- 补充price字段
    SELECT
        bi.`市场ID`,
        bi.row_id,
        JSON_MERGE_PRESERVE(bi.item, JSON_OBJECT('price', pp.`价格`)) AS item_with_price
    FROM basket_items bi
    LEFT JOIN product_prices pp ON bi.article_id = pp.article_id
)
-- 聚合为JSON数组
SELECT
    `市场ID`,
    JSON_ARRAYAGG(item_with_price) AS `购物篮位置`
FROM items_with_price
GROUP BY `市场ID`, row_id
ORDER BY `市场ID`, row_id;

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