时间戳

首页 > 解决方案 > 从同一个表中的其他列更新 JSONB 列数据

json - 从同一个表中的其他列更新 JSONB 列数据

问题描述

在Postgres 9.5中，我想用同一张表中其他列的数据更新一列。问题在于""符号，因为在 JSON 字符串中它不允许从实际列中读取数据：

CREATE TABLE sample_table(
   id bigint
 , revision integer
 , var_data text
 , json_result jsonb
);

样本数据：

id  revision  var_data  json_result
1     10        test_A   {"EstimatedResult": 10, "RevOnly": 10}
2     20        test_B   {"EstimatedResult": 20, "RevOnly": 20}
3     30        test_C   {"EstimatedResult": 30, "RevOnly": 30}

示例 1：这是有效的： 

UPDATE "public"."sample_table"
SET "json_result" = "json_result" - 'EstimatedResult'
                  || jsonb_build_object('EstimatedResult', revision::numeric)::jsonb
WHERE json_result->>'EstimatedResult' IS NOT NULL;

结果json_result：

{"EstimatedResult": 10, "RevOnly": 10}
{"EstimatedResult": 20, "RevOnly": 20}
{"EstimatedResult": 30, "RevOnly": 30}

示例 2：这无法正常工作：

要求是修改 json 结构以插入来自多列的数据。结果应该是这样的：

{"EstimatedResult": {"revid": 10, "displ": "test_A"}, "RevOnly": 10}
{"EstimatedResult": {"revid": 20, "displ": "test_B"}, "RevOnly": 20}
{"EstimatedResult": {"revid": 30, "displ": "test_C"}, "RevOnly": 30}

我知道如何使用 Example1 从“RevOnly”中的列中强制数据。但是，我没有成功强制列中的数据是“revid”和“displ”

我成功生成了 JSON 值，但结果是这样的：

{"EstimatedResult": {"revid": "revision", "displ": "var_data"}, "RevOnly": 10}
{"EstimatedResult": {"revid": "revision", "displ": "var_data"}, "RevOnly": 20}
{"EstimatedResult": {"revid": "revision", "displ": "var_data"}, "RevOnly": 30}

注意“revision”和“var_data”没有更新。

如何正确更新？

标签： jsonpostgresqlsql-updatejsonb

解决方案

假设至少 Postgres 9.5和 data type jsonb。
一种优雅的方式是与jsonb_set()- 结合jsonb_build_object()你已经成功使用：

SELECT jsonb_set(json_result
              , '{EstimatedResult}'
              , jsonb_build_object('revid', revision, 'displ', var_data))
FROM   sample_table;

推荐阅读