如何更新 JSONB 以将新键添加到所有记录的嵌套数组(对于数组的所有项目)中。
我指的是link表结构为:
CREATE TABLE orders (
id serial PRIMARY KEY,
data jsonb
);
给定的 json 是:
{
"Number": "555",
"UserId": "1",
"Items": [
{
"ProductId": "1",
"Name": "TV",
"Price": "300.00"
},
{
"ProductId": "2",
"Name": "Mechanical Keyboard",
"Price": "120.00"
}
]
}
要将新元素添加到每个数组项中,给出以下查询:
UPDATE orders
SET data = jsonb_set(
data,
'{Items}', -- the array in which we operate
to_jsonb(
(WITH ar AS(
WITH temp AS(
SELECT data->'Items' AS items -- the array in which we operate
FROM orders
WHERE id = 1 -- the filtered order we are updating
)
SELECT jsonb_set(
jsonb_array_elements(items),
'{Quantity}', -- the new field we are adding
'"1"', -- the value of the new field
true)
FROM temp)
SELECT (array_agg(ar.jsonb_set))
FROM ar)),
false)
WHERE id = 1;
执行上述查询后的输出:
{
"Number": "555",
"UserId": "1",
"Items": [
{
"ProductId": "1",
"Name": "TV",
"Price": "300.00",
"Quantity": "1"
},
{
"ProductId": "2",
"Name": "Mechanical Keyboard",
"Price": "120.00",
"Quantity": "1"
}
]
}
但上面只会更新 jsonid=1
。需要进行哪些更改才能更新订单中所有行的 JSON ?