title: Updating a nested array element in a jsonb column with postgres 12.10
body: <p>I am trying to update a record in an array.  The table is <code>participant</code> and the column is <code>activities</code> with the shape:</p>
  <pre><code>{
    enrolled: [{
      sport: {
        id: 1,
        name: 'soccer'
      }
    }, {
      sport: {
        id: 2,
        name: 'hockey'
      }
    }]
  }
  </code></pre>
  <p>I'd like to run a query that says,</p>
  <blockquote>
  <p>If activities[*].enrolled.sport == 1, add some JSON blob for the registered day</p>
  </blockquote>
  <pre><code>registered: {
    day: 12,
    month: 'Aug'
  }
  </code></pre>
  <p>The resulting object would be:</p>
  <pre><code>{
    enrolled: [{
      sport: {
        id: 1,
        name: 'soccer'
      },
      registered: {
        day: 12
        month: 'Aug'
      }
    }, {
      sport: {
        id: 2,
        name: 'hockey'
      }
    }]
  }
  </code></pre>
  <p>I've tried the following but it replaces the entire column with the added object :(</p>
  <pre><code>set activities = (
      select jsonb_agg(jsonb_set(sports, '{registered}', '{&quot;day&quot;: 12, &quot;month&quot;: &quot;Aug&quot;}', true))
      from jsonb_array_elements(activities::jsonb -&gt; 'enrolled') sports
  )
  where activities::jsonb -&gt; 'enrolled' @? '$.sport.id ? (@ == 1)';
  </code></pre>