PostgreSQL 12中遍历jsonb对象数组并转换type值为小写

没问题，当然可以实现！你原来的SQL问题出在聚合逻辑上——你把每个键值对单独聚合成了独立的小对象，而没有先将每个数组元素的键值对重新组合成完整的对象，再聚合回数组。这里有两种高效的解决方法：

方法1：使用jsonb_set直接修改指定字段（推荐） #

这种方法不需要遍历对象的所有键值对，直接定位到type字段进行修改，代码更简洁高效：

SELECT jsonb_agg(
  jsonb_set(elem, '{type}', to_jsonb(lower((elem->>'type')::text)))
)
FROM jsonb_array_elements(
  '[{"data":"42","type":"TEMPERATURE"},{"data":"1.1","type":"PRESSURE"}]'::jsonb
) AS arr(elem);

步骤说明： #

1. jsonb_array_elements：将原始JSONB数组拆分成单个的JSONB对象（每个elem对应数组里的一个元素）
2. jsonb_set：对每个对象执行修改操作：
   • 第一个参数elem：要修改的目标对象
   • 第二个参数'{type}'：JSON路径，指定要修改的type字段
   • 第三个参数：先通过elem->>'type'取出type的文本值，用lower()转小写后，再用to_jsonb()转回JSONB类型
3. jsonb_agg：将修改后的单个对象重新聚合成完整的JSONB数组

方法2：遍历键值对后重组对象 #

如果你需要处理多个字段的场景，或者不确定对象的字段数量，可以用这种遍历重组的方式，核心是按原始元素分组，先把处理后的键值对重组为单个对象，再聚合数组：

SELECT jsonb_agg(obj)
FROM (
  SELECT jsonb_object_agg(
    k,
    CASE WHEN k = 'type' THEN to_jsonb(lower(v::text)) ELSE v END
  ) AS obj
  FROM jsonb_array_elements(
    '[{"data":"42","type":"TEMPERATURE"},{"data":"1.1","type":"PRESSURE"}]'::jsonb
  ) AS arr(elem),
  LATERAL jsonb_each(elem) AS p(k, v)
  GROUP BY elem -- 关键：按原始元素分组，确保每个对象的键值对被重组到一起
) AS sub;

步骤说明： #

1. 同样先拆分数组为单个对象，再拆分每个对象的键值对
2. CASE语句：判断当前键是否为type，是则转小写，否则保留原内容
3. jsonb_object_agg + GROUP BY elem：将同一原始元素的所有键值对重新组合成一个完整的JSONB对象
4. 最后用jsonb_agg将所有重组后的对象聚合成数组

两种方法都能得到你想要的结果：[{"data":"42","type":"temperature"},{"data":"1.1","type":"pressure"}]

内容的提问来源于stack exchange，提问作者y.bregey