时间戳

首页 > 解决方案 > 如何在 Postgres 的 Json 类型列中添加键值对

sql - 如何在 Postgres 的 Json 类型列中添加键值对

问题描述

我在 Postgres 数据库（9.4.9）中有一个 json 类型的列（状态）。我想为现有的状态值添加新的键值对。例子：

现有状态：

"status": {
            "keysterStatus": "In Progress"
          }

添加键值对后，我希望它看起来像这样

"status": {
            "provisioningStatus": "In Progress",
            "keysterStatus": "In Progress"
          }

到目前为止，我一直在使用存储库save()方法来完成这项工作，但这是在写入整行，并且在多个请求的情况下有可能并发读写。所以想摆脱 save() 方法并进行列级更新。

标签： sqljsonpostgresqlsql-update

解决方案

首先，PG9.4 已经过时，甚至现在还没有更新。PG9.5 包含 json_set 函数：

SELECT jsonb_set(status::jsonb,
                 '{provisioningStatus}',
                 to_jsonb('In Progress'))::jsonb
  FROM ....;

作为使用连接的可能性|| 转换为 jsonb 并返回：

SELECT (status::jsonb || '{"provisioningStatus": "In Progress"}')::json
  FROM ....;

对于 PG9.4，如果你知道 json 的模式，你可以使用 json_populate_record/row_to_json ：

SELECT (
         SELECT row_to_json(r)
           FROM (
                  SELECT r.*, 'In Progress' AS provisioningStatus
                    FROM json_populate_record(null::myrowtype, status) AS r
                ) AS r
       ) AS result
  FROM ....

或者你可以使用 json_each_text：

SELECT (
         SELECT json_object_agg(key, value)
           FROM (
                  SELECT *
                    FROM json_each_text(status)
                   UNION ALL
                  SELECT 'provisioningStatus', 'In Progress'
                ) AS a
        ) AS result
   FROM ...

可能最后一个（但丑陋的）方法只是将json转换为字符串，删除最后一个'}'，添加“provisioningStatus”：“In Progress”}'并转换回json：

SELECT (substr(status::text, 1, length(status::text) - 1)
          || ', "provisioningStatus": "In Progress"}')::json
  FROM ...

推荐阅读