title: Update a field in a JSON column in PostgreSQL
body: <p>I have a <em>work_item</em> table that has the following schema</p>
  <pre><code>+---------+----------+---------------+
  |  id     |   data   |   data_type   |
  +------------------------------------+
  |         |          |               |
  |         |          |               |
  |         |          |               |
  +---------+--------------------------+
  </code></pre>
  <p>and a <em>document_type</em> table with the following schema:</p>
  <pre><code>+---------+----------+
  |  id     | name     |
  +--------------------+
  |         |          |
  |         |          |
  |         |          |
  +---------+-----------
  </code></pre>
  <p>The data column is a <strong>json</strong> column that has a <strong>Type</strong> field. This is a sample column data:</p>
  <pre><code>{&quot;Id&quot;:&quot;5d35a41f-3e91-4eda-819d-0f2d7c2ba55e&quot;,&quot;WorkItem&quot;:&quot;24efa9ea-4291-4b0a-9623-e6122201fe4a&quot;,&quot;Type&quot;:&quot;Tax Document&quot;,&quot;Date&quot;:&quot;4/16/2009&quot;}
  </code></pre>
  <p>I need to update data columns whose <code>data_type</code> column value is <strong>DocumentModel</strong>  and <strong>Type</strong> field values matches a value in the name column of the <em>document_type</em> table to a json object containing the document_type id and the document_type name. Something like this <code>{&quot;id&quot;: &quot;&lt;doc_type_id&gt;&quot;, name: &quot;&lt;doc_type_name&gt;&quot;}</code>.</p>
  <p>I tried to do this by executing this query:</p>
  <pre class="lang-sql prettyprint-override"><code>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 -&gt;&gt; 'Type'::text = dt.name::text
  ), false)
  WHERE wi.data_type = 'DocumentModel'; 
  </code></pre>
  <p>The above script runs without an error. However, what it does is something unwanted, it changes the <em>data</em> and <em>data_type</em> columns to <em>null</em> instead of updating the <em>data</em> column.</p>
  <p>What is the issue with my script? Or can you suggest a better alternative to do a the desired update?</p>