如何通过PostgreSQL查询更新JSON数组中指定实体的email字段？

针对你的需求，我们可以利用PostgreSQL的JSON函数拆分、处理并重新聚合JSON数组，完成对指定字段的修改。以下是具体的更新语句：

UPDATE workflows_emails
SET "to" = (
  SELECT json_agg(
    CASE
      -- 匹配type为external的对象，修改email字段
      WHEN obj->>'type' = 'external' AND obj->>'email' IS NOT NULL
      THEN obj || json_build_object('email', (obj->>'email') || '.SBX')
      -- 其他类型的对象保持原样
      ELSE obj
    END
  )
  FROM json_array_elements("to") AS obj
);

语句说明： #

• json_array_elements("to")：将to列的JSON数组拆分为单个JSON对象，逐个处理
• CASE条件判断：检查对象的type是否为external，同时确保email字段存在（避免出现null.SBX的无效值）
• obj || json_build_object(...)：用新的email值覆盖原对象中的email字段（PostgreSQL中||运算符可用于合并JSON对象）
• json_agg(...)：将处理后的单个JSON对象重新聚合成数组，替换原to列的值

注意：因为to是PostgreSQL的关键字，所以必须用双引号"to"引用该列名。

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