Using jsonb_set() for updating specific jsonb array value
<p>Currently I am working with PostgreSQL 9.5 and try to update a value inside an array of a jsonb field. But I am unable to get the index of the selected value</p>
<p>My table just looks like this:</p>
<pre><code> CREATE TABLE samples (
    id serial,
    sample jsonb
 );
</code></pre>
<p>My JSON looks like this:</p>
<pre><code>{&quot;result&quot;: [
    {&quot;8410&quot;: &quot;ABNDAT&quot;, &quot;8411&quot;: &quot;Abnahmedatum&quot;},
    {&quot;8410&quot;: &quot;ABNZIT&quot;, &quot;8411&quot;: &quot;Abnahmezeit&quot;},
    {&quot;8410&quot;: &quot;FERR_R&quot;, &quot;8411&quot;: &quot;Ferritin&quot;}
]}
</code></pre>
<p>My SELECT statement to get the correct value works:</p>
<pre><code>SELECT 
    id, value 
FROM 
    samples s, jsonb_array_elements(s.sample#&gt;'{result}') r  
WHERE 
    s.id = 26 and r-&gt;&gt;'8410' = 'FERR_R';
</code></pre>
<p>results in:</p>
<pre><code>id | value
----------------------------------------------
26 | {&quot;8410&quot;: &quot;FERR_R&quot;, &quot;8411&quot;: &quot;Ferritin&quot;}
</code></pre>
<p>Ok, this is what I wanted. Now I want to execute an update using the following UPDATE statement to add a new element &quot;ratingtext&quot; (if not already there):</p>
<pre><code>UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,2,ratingtext}',
              '&quot;Some individual text&quot;'::jsonb,
              true)
WHERE
      s.id = 26;
</code></pre>
<p>After executing the UPDATE statement, my data looks like this (also correct):</p>
<pre><code>{&quot;result&quot;: [
    {&quot;8410&quot;: &quot;ABNDAT&quot;, &quot;8411&quot;: &quot;Abnahmedatum&quot;},
    {&quot;8410&quot;: &quot;ABNZIT&quot;, &quot;8411&quot;: &quot;Abnahmezeit&quot;},
    {&quot;8410&quot;: &quot;FERR_R&quot;, &quot;8411&quot;: &quot;Ferritin&quot;, &quot;ratingtext&quot;: &quot;Some individual text&quot;}
]}
</code></pre>
<p>So far so good, but I <strong>manually</strong> searched the index value of 2 to get the right element inside the JSON array. If the order will be changed, this won't work.</p>
<p><strong>So my problem:</strong></p>
<p>Is there a way to get the index of the selected JSON array element and combine the SELECT statement and the UPDATE statement into one?</p>
<p>Just like:</p>
<pre><code>UPDATE 
    samples s
SET
    sample = jsonb_set(sample,
              '{result,' || INDEX OF ELEMENT || ',ratingtext}',
              '&quot;Some individual text&quot;'::jsonb,
              true)
WHERE
      s.id = 26;
</code></pre>
<p>The values of <em>samples.id</em> and <em>&quot;8410&quot;</em> are known before preparing the statement.</p>
<p>Or is this not possible at the moment?</p>