postgresql - 如何将 JSONB 对象中的现有时间戳更改为 Postgresql 中的另一种时间戳格式

Question

表user_record有一个列info，它返回以下数据，而info它是一个jsonb列。
查询： Select info from user_record;
结果：

{"someId": "XXFUN0123XX", "age": 43, "updatedAt": "2017-02-18 00:00:00"}

现在问题是关于updatedAtjson响应
select info ->> 'updatedAt' from user_record where id='XXFUN0123XX';
结果的字段：2017-02-18 00:00:00

我必须使用这种格式更新表列updatedAt字段的所有时间戳 ->所以当我这样做时，我会得到这样的结果 -> 。 我已经尝试了多种方法并阅读了文档，但找不到合适的解决方案。以下是我试图做的一个查询infouser_recordYYYY-MM-DDTHH:MI:SS+02:00select info ->> 'updatedAt'2017-02-18T00:00:00+02:00

UPDATE user_record SET info = info || jsonb_build_object('updatedAt', 
select to_timestamp(info ->> 'updatedAt','YYYY-MM-DDTHH:MM:SS+02:00') from user_record where id ='XXFUN0123XX')) where id = 'XXFUN0123XX';

在上面的查询中，我试图updatedAt从内部查询中提取 json 对象。

标签： postgresqlpostgresql-9.5

Answer 1

用于jsonb_set在 jsonb 中进行更改。并使用to_char而不是to_timestamp像下面这样：

update user_record
set info=jsonb_set(info,'{updatedAt}',to_jsonb(to_char((info ->> 'updatedAt')::timestamp,'YYYY-MM-DD"T"HH24:MI:SS+02:00')))

演示