PostgreSQL中如何通过jsonb_set更新JSONB数组指定id的元素？

我完全理解你的困扰：当需要更新jsonb数组里的特定元素时，必须知道它的索引，但用generate_series遍历整个数组来查找索引的方式，在数据量大的时候性能开销实在太大。下面分享几个更高效的解决方案：

1. 用jsonb_array_elements + WITH ORDINALITY快速获取元素索引 #

WITH ORDINALITY是PostgreSQL处理集合返回函数的利器，它会直接返回每个元素在数组中的位置（注意是1-based的，所以要减1得到常用的0-based索引），而且只会遍历数组中实际存在的元素，比generate_series生成全量序列的方式高效得多。

先试试用这个方法查询目标元素的索引：

SELECT 
  a.id, 
  (elem.index - 1) AS array_index  -- 转换为0-based索引
FROM audits a
JOIN LATERAL jsonb_array_elements(a.data->'products') WITH ORDINALITY AS elem(item, index)
  ON elem.item->>'id' = '347'
WHERE a.id = 10 AND a.enterprise_id = 1;

2. 结合CTE实现高效更新 #

把上面的索引查询和更新操作结合起来，用CTE（公共表表达式）先定位目标记录和对应的索引，再执行更新，避免重复扫描表：

WITH target_elements AS (
  SELECT 
    id, 
    (index - 1) AS array_index
  FROM audits
  JOIN LATERAL jsonb_array_elements(data->'products') WITH ORDINALITY AS elem(item, index)
    ON elem.item->>'id' = '347'
  WHERE id = 10 AND enterprise_id = 1
)
UPDATE audits a
SET data = jsonb_set(
  a.data, 
  array['products', array_index::text, 'real_value'],  -- 把索引转为字符串组成路径
  '"125"'::jsonb
)
FROM target_elements te
WHERE a.id = te.id AND a.enterprise_id = 1;

3. 长期优化：考虑拆分jsonb数组为关系表 #

如果你的业务经常需要对这个数组里的元素进行查询、更新操作，建议把products数组拆分成独立的关系型表（比如audit_products），字段包括audit_id、enterprise_id、product_id、color、price等。这样不仅查询和更新的性能会大幅提升（可以利用主键、普通索引），还能避免jsonb操作的额外开销。如果需要保留原有的jsonb字段，可以用触发器来同步关系表和jsonb数据，或者直接用关系表作为唯一数据源。

为什么原来的方法性能差？ #

你之前用generate_series的方式，会先生成从0到数组长度-1的所有整数，然后逐个去匹配元素的id，当数组元素很多时，这个过程会做大量无效的匹配操作。而jsonb_array_elements + WITH ORDINALITY是直接遍历数组中的每个元素，只做必要的匹配，性能差异会随着数组大小的增加而越来越明显。

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