PL/pgSQL函数：将JSONB参数转为数组并遍历元素的最优方法

嘿，这个需求我刚好在项目里处理过，给你分享几个高效的实现思路，完美适配你说的两种JSON结构：

不管输入是直接的产品数组，还是嵌套在products键下的对象，我们可以先通过jsonb_typeof判断类型，再统一提取出产品数组。这一步是关键，能让后续逻辑不用区分输入结构。

1. 提取统一的产品数组 #

用COALESCE结合类型判断，一行代码就能搞定两种结构：

COALESCE(
  -- 如果输入本身是数组，直接用
  CASE WHEN jsonb_typeof(input_json) = 'array' THEN input_json ELSE NULL END,
  -- 如果是对象，取products键对应的数组
  input_json -> 'products'
) AS product_array

这个表达式会自动适配两种输入，返回的都是产品元素组成的JSON数组。

2. 遍历并转换产品数据 #

接下来要把数组里的每个元素拆成可用于比对的字段。这里推荐用jsonb_array_elements展开数组，再提取id_product和处理d_price（注意把逗号替换成点再转成数值类型）：

SELECT 
  (prod->>'id_product')::bigint AS id_product,
  -- 把逗号分隔的价格转成numeric类型，方便和表中数值比对
  REPLACE(prod->>'d_price', ',', '.')::numeric AS d_price
FROM jsonb_array_elements(
  COALESCE(
    CASE WHEN jsonb_typeof(input_json) = 'array' THEN input_json ELSE NULL END,
    input_json -> 'products'
  )
) AS prod

3. 整合到PL/pgSQL函数中 #

下面是一个完整的函数示例，直接可以用它来和你的产品表比对价格：

CREATE OR REPLACE FUNCTION compare_product_prices(input_json jsonb)
RETURNS TABLE(
  id_product bigint,
  table_price numeric,
  input_price numeric,
  price_is_match boolean
) AS $$
BEGIN
  -- 处理可能的无效JSON输入
  BEGIN
    -- 如果参数是text类型的话，先转成jsonb；如果参数已经是jsonb可以去掉这行
    input_json := input_json::jsonb;
  EXCEPTION
    WHEN others THEN
      RAISE NOTICE '输入的JSON格式无效，请检查';
      RETURN;
  END;

  RETURN QUERY
  SELECT 
    p.id_product,
    p.d_price AS table_price,
    REPLACE(prod->>'d_price', ',', '.')::numeric AS input_price,
    -- 直接比对价格是否相等
    (p.d_price = REPLACE(prod->>'d_price', ',', '.')::numeric) AS price_is_match
  FROM jsonb_array_elements(
    COALESCE(
      CASE WHEN jsonb_typeof(input_json) = 'array' THEN input_json ELSE NULL END,
      input_json -> 'products'
    )
  ) AS prod
  -- 关联你的产品表，这里假设表名为product_prices，字段是id_product和d_price
  JOIN product_prices p 
    ON p.id_product = (prod->>'id_product')::bigint;
END;
$$ LANGUAGE plpgsql;

4. 测试与优化 #

测试示例 #

测试第一种输入（直接数组）：

SELECT * FROM compare_product_prices('[
  {"id_product": 100000158, "d_price": "7,75"}, 
  {"id_product": 100000339, "d_price": "9,76"}
]'::jsonb);

测试第二种输入（嵌套对象）：

SELECT * FROM compare_product_prices('{
  "products": [
    {"id_product": 100000158, "d_price": "7,75"}, 
    {"id_product": 100000339, "d_price": "9,76"}
  ]
}'::jsonb);

优化建议 #

• 优先用jsonb类型作为参数，比json支持更多高效操作，还能建GIN索引（如果需要频繁查询JSON内容的话）。
• 给产品表的id_product字段建索引，这样关联比对的速度会快很多，尤其是数据量大的时候。
• 如果需要处理不存在的产品ID，可以加LEFT JOIN代替JOIN，这样会返回所有输入的产品，包括表中没有的。

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