时间戳

首页 > 解决方案 > 更新 PostgreSQL 中 JSON 列中的字段

sql - 更新 PostgreSQL 中 JSON 列中的字段

问题描述

我有一个具有以下架构的work_item表

+---------+----------+---------------+
|  id     |   data   |   data_type   |
+------------------------------------+
|         |          |               |
|         |          |               |
|         |          |               |
+---------+--------------------------+

和具有以下架构的document_type表：

+---------+----------+
|  id     | name     |
+--------------------+
|         |          |
|         |          |
|         |          |
+---------+-----------

数据列是具有类型字段的json列。这是一个示例列数据：

{"Id":"5d35a41f-3e91-4eda-819d-0f2d7c2ba55e","WorkItem":"24efa9ea-4291-4b0a-9623-e6122201fe4a","Type":"Tax Document","Date":"4/16/2009"}

我需要将data_type列值为DocumentModel 且Type字段值与document_type表的 name 列中的值匹配的数据列更新为包含 document_type id 和 document_type 名称的 json 对象。像这样的东西{"id": "<doc_type_id>", name: "<doc_type_name>"}。

我试图通过执行这个查询来做到这一点：

UPDATE wf.work_item wi
SET data = jsonb_set(data::jsonb, '{Type}', (
    SELECT jsonb_build_object('id', dt.id, 'name', dt.name) 
      FROM wf.document_type AS dt 
     WHERE wi.data ->> 'Type'::text = dt.name::text
), false)
WHERE wi.data_type = 'DocumentModel';

上面的脚本运行没有错误。但是，它所做的是不需要的，它将data和data_type列更改为null而不是更新数据列。

我的脚本有什么问题？或者您能否建议一个更好的替代方案来进行所需的更新？

标签： sqljsondatabasepostgresqljsonb

解决方案

document_type当表格中缺少文档类型时，就会出现问题。然后jsonb_set()返回null（因为子查询没有给出任何结果）。更安全的解决方案是使用以下from子句update：

update wf.work_item wi
set data = jsonb_set(
    data::jsonb, 
    '{Type}',
    jsonb_build_object('id', dt.id, 'name', dt.name),
    false)
from wf.document_type as dt 
where wi.data_type = 'DocumentModel'
and wi.data ->> 'Type'::text = dt.name::text;

推荐阅读