Postgres 10

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?.

create table t( id int, jsdata jsonb );
insert into t values( 1, '[{"a":"val1"}, {"b":"val2"}, {"c":"val3"}]'::jsonb );
insert into t values( 2, '[{"a":"val1"}, {"b":"val2"}]'::jsonb );
1 rows affected

1 rows affected

select id,
       jsonb_agg(
       (replace(jj.value,'}',',')||replace(jsonb_set(value2::jsonb,'{x}',
       ('"x'||(jj.value2::jsonb->>'x')::text||'"')::jsonb)::text,'{',''))::jsonb) 
    as result
  from
  (
   select t.id, j.value, regexp_replace(j.value,'[[:alpha:]]+','x') as value2
     from t
    cross join jsonb_array_elements_text(jsdata) j
  ) jj
 group by id 


id
result


2
[{"a": "val1", "x": "xval1"}, {"b": "val2", "x": "xval2"}]

1
[{"a": "val1", "x": "xval1"}, {"b": "val2", "x": "xval2"}, {"c": "val3", "x": "xval3"}]

id	result
2	[{"a": "val1", "x": "xval1"}, {"b": "val2", "x": "xval2"}]
1	[{"a": "val1", "x": "xval1"}, {"b": "val2", "x": "xval2"}, {"c": "val3", "x": "xval3"}]