JSON阵列内变化JSON对象值
问题描述:
我有orders
一个表:JSON阵列内变化JSON对象值
postgres=# \d orders
Table "public.orders"
Column | Type | Modifiers
--------------------+-----------------------------+-------------------------------------
id | uuid | not null default uuid_generate_v4()
production_details | jsonb |
production_details
我被表示为如下:
postgres=# SELECT production_details FROM orders;
{
"data":[
{
"id":"1a24586c-c917-45d0-93d9-2d969fa6959d",
"quantity":10,
"production_at":"2016-04-17T00:00:00.000+00:00"
},
...
]
}
并且对于每个production_detail
我想改变timestamp
到只是date
。
我知道我可以选择所有production_at
为:
SELECT (jsonb_array_elements(production_details->'data')->>'production_at') FROM orders;
然而,如何及时更新这些JSON?
答
解压数组,编辑元素并重新构建整个对象。
示例数据:
create table orders (id int, details jsonb);
insert into orders values (1,
'{
"data":[
{
"id":"1a24586c-c917-45d0-93d9-2d969fa6959d",
"quantity":10,
"production_at":"2016-04-17T00:00:00.000+00:00"
},
{
"id":"1a24586c-c917-45d0-93d9-2d969fa6959x",
"quantity":20,
"production_at":"2016-04-18T00:00:00.000+00:00"
}
]
}');
查询:
update orders o
set details = (
select
json_build_object(
'data',
jsonb_agg(
jsonb_set(
e,
'{production_at}',
to_jsonb((e->>'production_at')::timestamp::date::text))
)
)
from orders, jsonb_array_elements(details->'data') e
where id = o.id
);
结果:
select id, jsonb_pretty(details) from orders;
id | jsonb_pretty
----+-----------------------------------------------------------
1 | { +
| "data": [ +
| { +
| "id": "1a24586c-c917-45d0-93d9-2d969fa6959d",+
| "quantity": 10, +
| "production_at": "2016-04-17" +
| }, +
| { +
| "id": "1a24586c-c917-45d0-93d9-2d969fa6959x",+
| "quantity": 20, +
| "production_at": "2016-04-18" +
| } +
| ] +
| }
(1 row)
如果我想更新的所有订单,而无需指定一个特定的ID?因为当我删除'WHERE'子句时,内部'SELECT'选择所有的命令。 – squixy
我已经编辑了答案。查询现在更新所有行。 – klin