step-by-step demo:db<>fiddle

UPDATE t                                                                   -- 5SET mydata = s.new_dataFROM (    SELECT        id,        json_agg((using_updated - 'used_when')::json) as new_data          -- 4    FROM t,        json_array_elements(t.mydata) as elements,                         -- 1        jsonb_set(elements::jsonb, '{tax}',                                -- 2            CASE                WHEN elements ->> 'tax' = 'yes' THEN 'true'::jsonb                ELSE 'false'::jsonb            END        ) as tax_updated,        jsonb_set(tax_updated::jsonb, '{using}',                           -- 3            CASE                WHEN tax_updated ->> 'used_when' = 'true' THEN 'true'::jsonb                ELSE 'false'::jsonb            END        ) as using_updated    GROUP BY id) sWHERE s.id = t.id;

1. Extract all array elements into one element per record2/3. Now you can usejsonb_set() to insert new or update existing attributs within the array elements. TheCASE clause does the condition check
2. Eliminate the remainingused_when elements. Afterwards you can reaggregate the updated elements with json_agg
3. Do theUPDATE

If your data is type json, you have to do the casts to jsonb since, there is nojson_set(). If not, you can ignore the casts, of course.

However, as @a_horse_with_no_name correctly mentioned: You should think about not storing these data as pure JSON but extract them into a normalized relative database table structure, which make things much easier and more performant.

• sql
• postgresql
• jsonb
• postgresql-9.5