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

create table table_a (id int, name text, b_codes text);

CREATE TABLE

insert into table_a
values
(1, 'abc', 'a,b'),
(2, 'def', null);

INSERT 0 2

create table Table_B (code text, name text);

CREATE TABLE

insert into table_b
values
('a','xx'),
('b','yy');

INSERT 0 2

select jsonb_build_object('id', a.id, 'name', a.name, 'b_codes', coalesce(x.codes, '[]'))
from table_a a
  left join lateral (
    select jsonb_agg(jsonb_build_object('code', b.code, 'name', b.name)) as codes
    from unnest(string_to_array(a.b_codes, ',')) as c(code)
       left join table_b b on b.code = c.code
  ) as x on true;

jsonb_build_object

{"id": 1, "name": "abc", "b_codes": [{"code": "a", "name": "xx"}, {"code": "b", "name": "yy"}]}

{"id": 2, "name": "def", "b_codes": []}

SELECT 2