使用指定键更新Postgres中的JSON数组

Question

我有一个复杂的数组，看起来像下面的表列:

{
"sometag": {},
"where": [
{
"id": "Krishna",
"nick": "KK",
"values": [
"0"
],
"function": "ADD",
"numValue": [
"0"
]
},
{
"id": "Krishna1",
"nick": "KK1",
"values": [
"0"
],
"function": "SUB",
"numValue": [
"0"
]
}
],
"anotherTag": [],
"TagTag": {
"tt": "tttttt",
"tt1": "tttttt"
}

在这个数组中，我想更新id: "Krishna"的函数和numValue。

请帮助。

Answer 1

这真的很糟糕，因为

1. 更新JSON数组中的元素总是需要扩展数组
2. On-top:数组嵌套
3. 要更新的元素的标识符是兄弟元素而不是父元素，这意味着，你必须通过兄弟元素进行过滤

所以我想出了一个解决方案，但我想声明:您应该避免将此作为常规数据库操作!最好是:

1. 在后端解析JSON并在后端代码中执行操作
2. 规范化数据库中的JSON，如果这将是一个常见的任务，这意味着:创建具有适当列的表，并将JSON提取到表结构中。不要将整个JSON对象存储在数据库中!这将使每一项任务更容易，更高效!

演示:db<在小提琴>

SELECT
jsonb_set(                                                                                -- 5
(SELECT mydata::jsonb FROM mytable), 
'{where}', 
updated_array
)::json
FROM (
SELECT
jsonb_agg(                                                                            -- 4
CASE WHEN array_elem ->> 'id' = 'Krishna' THEN
jsonb_set(                                                                    -- 3
jsonb_set(array_elem.value::jsonb, '{function}', '"ADDITION"'::jsonb),    -- 2
'{numValue}', 
'["0","1"]'::jsonb
)
ELSE array_elem::jsonb END
) as updated_array
FROM mytable,
json_array_elements(mydata -> 'where') array_elem                                     -- 1
) s

1. 将嵌套数组元素提取为每行一个元素
2. 替换function值。注意从类型json到类型jsonb的转换。这是必要的，因为没有json_set()函数，只有jsonb_set()。当然，如果只有类型jsonb，则不需要强制转换。
3. 替换numValue值
4. 重新聚合数组
5. 将原JSON对象的where值替换为新创建的数组对象