在PostgreSQL中更新JSONB格式的对象数组

Question

我需要执行两个更新操作:

1. 更新将数据从字符串转换为bool
2. 基于其他标签使用新名称和数据重新创建标签

jsonb列中的示例数据:

[
{ "tax": "yes", "tax_percent": 20, "used_when": "after" },
{ "tax": "no", "tax_percent": 20 },
{ "tax_percent": 20, "used_when": "before" }
]

现在:

"tax"值需要从yes ->true, no或null(不存在)表示->假

"used_when"需要更新为使用"如果在->true，如果在OR之前为null(不存在)意味着->假

所以它看起来像:

[
{ "tax": true, "tax_percent": 20, "using": true },
{ "tax": false, "tax_percent": 20, "using": false },
{ "tax": false, "tax_percent": 20, "using": false }
]

这些值是可选的，所以不是所有条目都有它，而且这是数据库列中的单行，所以列中的数据需要为每一行更新。

Answer 1

分步演示:db<>fiddle

UPDATE t                                                                   -- 5
SET mydata = s.new_data
FROM (
SELECT
id,
json_agg((using_updated - 'used_when')::json) as new_data          -- 4
FROM t,
json_array_elements(t.mydata) as elements,                         -- 1
jsonb_set(elements::jsonb, '{tax}',                                -- 2
CASE
WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb
ELSE 'false'::jsonb
END
) as tax_updated,
jsonb_set(tax_updated::jsonb, '{using}',                           -- 3
CASE
WHEN tax_updated ->> 'used_when' = 'true' THEN 'true'::jsonb
ELSE 'false'::jsonb
END
) as using_updated
GROUP BY id
) s
WHERE s.id = t.id;

1. 将所有数组元素提取为每个记录的一个元素2/3。现在可以使用jsonb_set()在数组元素中插入新的或更新现有的属性。CASE条款进行条件检查
2. 清除剩余的used_when元素。之后，可以使用json_agg
重新聚合更新的元素
3. UPDATE

如果你的数据是json类型，你必须做转换到jsonb，因为没有json_set()。如果没有，当然可以忽略强制类型转换。

然而，正如@a_horse_with_no_name正确提到的那样:您应该考虑不将这些数据存储为纯JSON，而是将它们提取到规范化的相对数据库表结构中，这会使事情变得更容易，性能更高。