如何在单个更新查询中更新json字段中的多个嵌套键?

Question

我想写一个函数，更新一个json(不是jsonb)字段(称为settings)在一个表(称为user)。

我的json对象看起来像这样(但是它可能没有任何嵌套级别的一些键):

{
"audiences": ["val1", "val2"],
"inviteNumber": "123",
"workExperience": [
{
"company": {
"name": "Ace",
"industry": "Accounting",
"revenues": "1M-10M",
"size": "1-10"
},
"title": "Product",
"startDate": {
"month": 1,
"year": 2018
}
}
],
"notifications": {
"emailNotifications": true
},
"jobFunction": "Research & Development",
"phoneNumber": "2134447777",
"areasOfInterest": {
"Recruiting and Staffing": true
}
}

我需要能够更新标题"one_answers";name"工作经验"中第0个元素的字段;数组。

我现在拥有的是:

create or replace function my_function()
returns trigger language plpgsql as $$
declare
companyName character varying(255);
begin
select company.name into companyName from company where id = new.companyid;
update "user" set
email = new.email,
"settings" = jsonb_set(
"settings"::jsonb,
'{workExperience,0}'::TEXT[],
format(
'{"company": {"name": %s}, "title": %s}',
'"' || companyName || '"', '"' || new.title || '"'
)::jsonb
)
where id = new.userid;

return new;
end $$;

然而，上面的实现重写了whileworkExperience对象，删除了除company和title以外的键。

我试着在SO上查看这个答案，但仍然无法正确实现更新。

我看到问题在于jsonb_set是如何工作的，它只是我告诉它要做的:设置"workExperience"的第0个元素数组到我在format函数中定义的对象。似乎我需要在另一个内部使用多个jsonb_set，但我无法找出正确的方法。

---

我怎样才能正确地更新我的json字段，而不从对象中删除其他键?

Answer 1

jsonb_set()返回修改后的JSON值。

您可以嵌套调用并首先更改公司名称，然后将其结果用作另一个jsonb_set()的输入。

"settings" = jsonb_set(jsonb_set("settings"::jsonb, '{workExperience,0,company,name}', to_jsonb(companyname)), 
'{workExperience,0,title}', to_jsonb(new.title)
)