Update elements in a JSON array in PostgreSQL 15.2

Question

I need some help to update elements in a JSON array. I want to update each element in a JSON array based on the value of a parameter.

The table has 2 columns: id (bigint NOT NULL GENERATED ALWAYS AS IDENTITY) and dashnoard (jsonb).

Here the JSON stored in the column dashboard:

[
  {
    "role": "A",
    "state": "running",
    "reference": "1234"
  },
  {
    "role": "B",
    "state": "iddle",
    "reference": "1235"
  }
]

My request:

WITH items AS (     
    SELECT ('{'||index-1||',state}')::TEXT[] AS path    
    FROM my_table, jsonb_array_elements_text(dashboard) WITH ORDINALITY arr(item, index)    
    WHERE (item::JSONB->>'state' = 'iddle' OR item::JSONB->>'state' = 'running') AND id=5 
) 
UPDATE my_table
SET dashboard = jsonb_set(dashboard, items.path, '"canceled"', false) 
FROM items 
WHERE id=5; 

What I want : all (here the 2) elements of the JSON array with state in ('running', 'iddle') updated with state := 'canceled' What I have : only the first element of the JSON array updated with state := 'canceled'. The second stay unchange.

Thank you

Answer 1

You can do it by aggregate modified array elements with jsonb_agg()

WITH items AS (     
    SELECT id,
    jsonb_agg(
            case when (value->>'state' = 'iddle' OR value->>'state' = 'running') then value || '{"state": "canceled"}'
            else value
            end
           ) as dashboard
    FROM dashboard_mif
    cross join jsonb_array_elements(dashboard)  
    WHERE id=5
    group by id 
) 
update dashboard_mif m
set dashboard = n.dashboard
from items n
where m.id = n.id;

1- Extract the array into a set of JSON values using jsonb_array_elements

2- Update state if iddle or running using case statement

3- Rebuild the object

4- Reaggregate your array

Demo here