更新 json 数据 PSQL 中的值

Question

我有一个表，其中包含使用 psql 作为 JSON 存储在数据列中的表， 尝试更新对象数组(数据列(中的特定单元格。

例如，将"first_name："Fergus"更新为"first_name"："Marcus"，其中"id"："1">

CREATE TABLE tables(
table_id SERIAL PRIMARY KEY,
table_name VARCHAR(100) NOT NULL,
user_id VARCHAR(100) NOT NULL,
data json
);
INSERT INTO tables (table_name, user_id, data)
VALUES (
'Supplier Info', 4, '[
{"id":"1","first_name":"Fergus","last_name":"Flipsen","email":"[e-mail]","gender":"Male"},
{"id":"2","first_name":"Vincenz","last_name":"Russan","email":"[e-mail]","gender":"Male"}
]'::JSON);

Answer 1

这并不容易(而且，正如a_horse_with_no_name所评论的那样，使用规范化模式会简单得多(。

基本上，您需要取消嵌套内部数组，修改相关对象，然后聚合回数组。

您可以使用相关的子查询、jsonb_array_elements()和条件逻辑来执行此操作jsonb_agg()：

update tables t
set data = (
select 
jsonb_agg(
case when (x.obj ->> 'id')::int = 1 
then x.obj || '{"first_name": "Marcus"}'
else x.obj
end
order by x.ord
) new_data
from jsonb_array_elements(t.data) with ordinality x(obj, ord)
)

DB小提琴上的演示