时间戳

首页 > 解决方案 > 如何根据postgres中json数组中的键更新值？

sql - 如何根据postgres中json数组中的键更新值？

问题描述

在一个表中有一个列 data(jsonb) 和这样的 json 数组

[
  {"pid": "123", "percentage": "10"}, 
  {"pid": "456", "percentage": "50"}, 
  {"pid": "789", "percentage": "40"}
]

我想更新 pid 为 789 的百分比 30。我使用了这个查询但没有成功。

UPDATE table
SET data =
jsonb_set(data, '{pid}'::text[], data->'pid' || '{"percentage":"30"}'::jsonb)
WHERE (data->> 'pid') = '789' and id= '1'; [id is table's primary key]

标签： sqljsonpostgresql-9.5

解决方案

没有简单的方法可以做到这一点（除了将您的数据模型更改为正确规范化的模型）。您将不得不取消嵌套数组，并替换相关 PID 的百分比。然后将元素聚合回一个数组。

您也不能->>在数组上使用，因为该运算符不适用于数组。

update the_table t
   set data = (select jsonb_agg(case d.element ->> 'pid' 
                                  when '789' then d.element || '{"percentage": 30}'
                                  else d.element 
                                end)
               from jsonb_array_elements(t.data) as d(element))
where id = 1
  and data @> '[{"pid": "789"}]'

推荐阅读