Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

drop table if exists my_table;
create table my_table(json_col jsonb);
insert into my_table(json_col) values
('{"grades": ["Pre-K", "K", "4th", "5th", "6th", "7th", "8th"]}'),
('{"grades": ["Pre-K", "K", "1st", "2nd", "3rd", "4th", "5th"]}');

2 rows affected

update my_table
set json_col = jsonb_set(
        json_col, 
        '{grades}', 
        (
            select jsonb_agg(regexp_replace(value, 're-|st|nd|rd|th', ''))
            from jsonb_array_elements_text(json_col->'grades')
        )
    );

2 rows affected

select *
from my_table;



json_col


{"grades": ["PK", "K", "4", "5", "6", "7", "8"]}

{"grades": ["PK", "K", "1", "2", "3", "4", "5"]}