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

Question

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

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

和具有以下架构的document_type 表:

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

数据列是一个 json 列，它有一个 Type 字段。这是示例列数据:

{"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 表的名称列中的值与包含 document_type id 和文档类型名称。像这样的 {"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 而不是更新 data 专栏。

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

Answer 1

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

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;