title: Postgresql replace an array of ids in jsonb with more columns
body: <p>Given a jsonb value in the source table like :</p>
  <pre><code>SOURCE_TABLE
  id | jsonb
  ---+------
   1 | {&quot;col_1&quot;: &quot;A&quot;, &quot;col_2&quot;: [&quot;A&quot;, &quot;B&quot;, &quot;C&quot;], &quot;col_3&quot;: [1, 2, 3]}
   2 | ...
  </code></pre>
  <p>and a lookup table:</p>
  <pre><code>LOOKUP_TABLE
  id | val_1 | val_2
  ---+-------+------
   1 |  aaa  |  AAA
   2 |  bbb  |  BBB
   3 |  ccc  |  CCC
  </code></pre>
  <p>I want to SELECT a new jsonb by replacing &quot;col_3&quot; ids with the details from the lookup table, like:</p>
  <pre><code>id | new_jsonb
  ---+------
   1 | {&quot;col_1&quot;: &quot;A&quot;, &quot;col_2&quot;: [&quot;A&quot;, &quot;B&quot;, &quot;C&quot;], &quot;col_3&quot;: [{&quot;val_1&quot;: &quot;aaa&quot;, &quot;val_2&quot;: &quot;AAA&quot;}, {&quot;val_1&quot;: &quot;bbb&quot;, &quot;val_2&quot;: &quot;BBB&quot;}, {&quot;val_1&quot;: &quot;ccc&quot;, &quot;val_2&quot;: &quot;CCC&quot;}]}
   2 | ...
  </code></pre>
  <p>How would you write the SQL statement?</p>