json - 如何在时代更新 Postgres 中的当前时间的 json 对象

Question

我需要用 Epoch 中的当前时间更新“lastUpdated”字段的 json 对象。我正在执行以下操作，但失败了:

UPDATE data SET data = data  || '{"lastUpdated": extract(epoch from current_timestamp) }'
 WHERE dtype='Employee' and data->>'id' = '1234';

以下内容本身不起作用:

UPDATE data SET data = data  || '{"lastUpdated": current_timestamp }'
 WHERE dtype='Employee' and data->>'id' = '1234';

我收到“json 类型的无效输入语法”。

请注意，当我设置自定义纪元时间时，以下内容有效:

UPDATE data SET data = data  || '{"dateAdded":1447502107000 , "lastUpdated":1447502107000}' WHERE dtype='Employee' and data->>'id' = '1234';

Answer 1

首先，您应该将 data 列的数据类型更改为 jsonb，因为 the concatenation operator || 不适用于 json。

alter table data alter data type jsonb;

然后使用函数jsonb_build_object():

UPDATE data SET data = data || jsonb_build_object('lastUpdated', extract(epoch from current_timestamp))
WHERE dtype='Employee' and data->>'id' = '1234';

如果不想更改列类型，则必须使用转换:

UPDATE data SET data = data::jsonb || jsonb_build_object('lastUpdated', extract(epoch from current_timestamp))
WHERE dtype='Employee' and data->>'id' = '1234';

---

更新

选择两个变体之一来舍入或去除时间戳的精度:

select
    extract(epoch from current_timestamp),
    replace(extract(epoch from current_timestamp::timestamp(3))::text, '.', ''),
    concat(extract(epoch from current_timestamp::timestamp(0))::text, '000')

    date_part     |    replace    |    concat     
------------------+---------------+---------------
 1533560841.14562 | 1533568041146 | 1533568041000
(1 row) 

或者:

select
    extract(epoch from current_timestamp),
    extract(epoch from current_timestamp(3))* 1000 as v1,
    extract(epoch from current_timestamp(0))* 1000 as v2