Update dynamic jsonb array of objects

Question

I am trying to update a jsonb key with a new value, but I cannot update all the keys at once.

My json structure is something like this:

[{
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"
    }
}, {
    "type": "button",
    "content": {
        "align": "leftAlign"

    }
}] 

I want to update the align key with a new value, but right now my query does not update all the elements, it only updates one element

this is my query so far:

with align_position as (
    select 
    ('{' || index-1 || ',content,align}' )::text[] as path,
    id
    from section, jsonb_array_elements(entities) with ordinality arr(entity, index)
    where entity->'content'->>'align' = 'leftAlign'
)
update myTable set entities = jsonb_set(entities, align_position.path, '"left"', false) from align_position where section.id = align_position.id;

How can I make the query to update all the elements?

Any thoughts?

Answer 1

I managed to solve this using this query:

update
   myTable 
set
   entities = 
   (
      select
         to_jsonb(array_agg (new_values)) 
      from
         (
            select
(
               case
                  when
                     arr -> 'content' ->> 'align' = 'leftAlign' 
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "left"}'), true), '{}' ) ) 
                  when
                     arr -> 'content' ->> 'align' = 'rightAlign' 
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "right"}'), true), '{}' ) ) 
                  when
                     arr -> 'content' ->> 'align' = 'centerAlign' 
                  then
                     jsonb( COALESCE( jsonb_set(arr, '{content}', jsonb(arr ->> 'content') - 'align' || jsonb('{"align": "center"}'), true), '{}' ) ) 
                  else
                     arr 
               end
) as new_values 
            from
               jsonb_array_elements( entities ) as arr 
               group by
                  arr -> 'content' ->> 'align',
                  arr 
         )
         as new_values 
   )
;