Postgres中如何用JSON列值作参数查询查找表替换item编码

核心逻辑分三步：拆解输入JSON数组提取旧编码、关联编码查找表匹配新编码、聚合重组为目标JSON结构。

前置说明 #

• 假设编码查找表名为item_code_mapping，包含字段：old_item_code（旧编码）、description（编码描述）、new_item_code（映射后的新编码）
• 以下示例默认使用PostgreSQL推荐的jsonb类型存储JSON数据，如果你用的是json类型，把所有jsonb_前缀的函数替换为json_前缀即可，逻辑完全一致

可直接运行的实现SQL #

WITH parsed_input AS (
    -- 1. 拆解输入的items数组，提取每个条目的旧编码和对应value
    SELECT
        item_elem->>'code' AS old_code,
        item_elem->>'value' AS item_val
    FROM jsonb_array_elements(
        -- 此处替换为你实际的JSON参数/字段值，示例为硬编码测试数据
        '{"items":[{"code":"OLDCODE1","description":"sample description1","value":"Sample value1"},{"code":"OLDCODE2","description":"Sample Description2","value":"Sample Value 2"}]}'::jsonb -> 'items'
    ) AS item_elem
),
code_matched AS (
    -- 2. 关联查找表，匹配每个旧编码对应的新编码
    SELECT
        map.new_item_code,
        pi.item_val
    FROM parsed_input pi
    JOIN item_code_mapping map
        ON pi.old_code = map.old_item_code
)
-- 3. 聚合重组为目标格式的JSON
SELECT jsonb_build_object(
    'items', jsonb_build_array(
        jsonb_object_agg(new_item_code, item_val)
    )
) AS final_result;

注意事项 #

• 上述SQL中用的INNER JOIN会自动过滤掉查找表中不存在对应映射的旧编码条目，如果需要保留未匹配条目，可替换为LEFT JOIN，按需给未匹配到新编码的条目设置默认值即可。
• 应用层调用时，把示例中硬编码的JSON字符串替换为对应框架的参数占位符即可，主流ORM框架都支持直接传入JSON类型参数绑定。
• 当查找表数据量较大时，建议给old_item_code字段加主键/唯一索引，关联匹配的性能会有明显提升。

结果示例 #

当查找表中OLDCODE1对应NEWCODE1、OLDCODE2对应NEWCODE2时，查询返回结果和目标格式完全一致：

{
  "items": [
    {
      "NEWCODE1": "Sample value1",
      "NEWCODE2": "Sample Value 2"
    }
  ]
}

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