postgresql 如何更新Postgres JSON数组中的特定实体

k2fxgqgv 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(1)|浏览(215)

我有一个Postgres 11.12数据库，其中有一个名为workflows_emails的表，表中有一个名为to的json列。

[
   {
      "type":"employee",
      "assignment":{
         "..."
      }
   },
   {
      "type":"external",
      "email":"mailbox@foobar.com"
   }
]

我想更新json数组，对所有type字段为external的实体中的email字段进行模糊处理。生成的数组如下所示：

[
   {
      "type":"employee",
      "assignment":{
         "..."
      }
   },
   {
      "type":"external",
      "email":"mailbox@foobar.com.SBX"
   }
]

有没有办法用Postgres查询来实现这一点？

postgresql

来源：https://stackoverflow.com/questions/74459734/how-to-update-specific-entities-in-a-postgres-json-array

1条答案

按热度按时间

mbjcgjjk1#

假设您的workflows_emails表有一个id主键列，您可以通过运行以下更新来更新数组中相应的JSON对象：

update workflows_emails set "to" = new_to.new_to
from (
    select id,
  json_agg(
    case
      when element ->> 'type' = 'external' then 
             jsonb_set(element, '{email}', to_jsonb(element ->> 'email' || '.SBX'))
      else element
    end
  ) as new_to
from workflows_emails,
     jsonb_array_elements("to") as element
group by id
) new_to
where new_to.id = workflows_emails.id

new_to中的子查询迭代JSON数组中的元素并转换type为external的对象。迭代是通过使用jsonb_array_elements解包JSON数组来完成的，这样我们就可以对各个元素计算条件和转换。转换适当的元素后，我们使用json_agg重新创建JSON数组。对于转换，我们使用jsonb_set，它将现有JSON对象的email字段设置为一个新值。
然后使用子查询得结果更新原始表得"to"列.
您可以在dbfiddle上看到结果。

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql 如何更新Postgres JSON数组中的特定实体

1条答案

相关问题

热门标签

最新问答