时间戳

首页 > 解决方案 > Postgres：使用给定的键集和默认值创建 jsonb 对象

json - Postgres：使用给定的键集和默认值创建 jsonb 对象

问题描述

我正在使用 PostgreSQL 9.6。我有一个ARRAY['a', 'b']::text[]来自应用程序代码的数组，并在 SQL 中进行了一些转换，所以我不知道它在应用程序代码中的长度。

在表中，我有一个类型的字段jsonb，我需要将其设置为 json 对象，其中键是给定数组中的值，并且值都相同并且等于当前时间戳，即

| id | my_field                                         |
---------------------------------------------------------
| 1  | {"a":"1544605046.21065", "b":"1544605046.21065"} |

我正在尝试查找更新查询来执行此更新，例如

UPDATE mytable 
SET my_field = some_function(ARRAY['a','b']::text[], EXTRACT(EPOCH FROM CURRENT_TIMESTAMP)
WHERE <some_condition>;

我正在查看jsonb_build_object函数，如果我可以转换我的数组，将它的元素与当前时间戳交错，这可能对我有帮助，但是我没有找到一种方法来做到这一点。

请注意，我可能要更新数十万条记录，因此我正在寻找一个快速的实现。

我将不胜感激有关此事的任何建议。

标签： jsonpostgresqljsonbpostgresql-9.6

解决方案

演示：db<>小提琴

UPDATE my_table
SET my_field = s.json_data
FROM (
    SELECT jsonb_object_agg(key, extract(epoch from current_timestamp)) as json_data
    FROM unnest(array['a', 'b']) as u(key)
) s
WHERE <some condition>
  1. 要将数组元素用作 json 对象的键，您需要通过 unnest 将它们分开。这将为每个元素创建一行。
  2. 用 聚合行jsonb_object_agg(key, value)。作为关键，您正在使用数组元素列。作为价值current_timestamp。此函数聚合成您预期的语法。
  3. 将其放入子查询中可以让您进行更新。

推荐阅读