postgresql - 如何在PostgreSQL中更新JSONB列，这只是一个值数组并且没有键

我需要更新一个名为“verticals”的jsonb列，它所包含的值数组如HOM、BFB等。数组中没有键。
表：Product(verticals jsonb, code int)
“垂直”列中存储的示例值为

[HOM,rst,NLF,WELSAK,HTL,TRV,EVCU,GRT]

我需要将“verticals”列中的值“HOM”更新为“XXX”，其中code=1
我的预期产出是

[XXX,rst,NLF,WELSAK,HTL,TRV,EVCU,GRT]

最佳答案

因为您选择以非规范化的方式存储数据，所以更新数据比必须更新数据更复杂。
您需要首先取消对数组的测试（基本上是对数据进行规范化），替换这些值，然后将它们聚合回来并更新列：

update product p
  set verticals = t.verticals
from (
  select jsonb_agg(case when x.v = 'HOM' then 'XXX' else x.v end order by idx) as verticals
  from product p2, jsonb_array_elements_text(p2.verticals) with ordinality as x(v,idx)
  where code = 1
) t
where p.code = t.code;

这假设product.code是一个主（或唯一）键！
在线示例：http://rextester.com/KZQ65481
如果数组元素的顺序不重要，则更容易：

update product
   set verticals = (verticals - 'HOM')||'["XXX"]'
where code = 1;

这将从数组中删除元素“HOM”（不管位置如何），然后将“XXX”追加到数组的末尾。