Update a field in a JSON column in PostgreSQL

Question

I have a work_item table that has the following schema

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

and a document_type table with the following schema:

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

The data column is a json column that has a Type field. This is a sample column data:

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

I need to update data columns whose data_type column value is DocumentModel and Type field values matches a value in the name column of the document_type table to a json object containing the document_type id and the document_type name. Something like this {"id": "<doc_type_id>", name: "<doc_type_name>"}.

I tried to do this by executing this query:

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'; 

The above script runs without an error. However, what it does is something unwanted, it changes the data and data_type columns to null instead of updating the data column.

What is the issue with my script? Or can you suggest a better alternative to do a the desired update?

Answer 1

The problem arises when the document type is missing from the document_type table. Then jsonb_set() returns null (as the subquery does not give any results). A safer solution is to use the from clause in 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;