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 the_table (name text, notes jsonb);
insert into the_table
values
('anna', '{"link_to": ["bob"]}'),
('bob', '{"link_to": ["anna", "claudia"]}'),
('claudia', '{"link_to": []}');
3 rows affected
select name,
notes,
notes -> 'link_to' as referrals_to,
(select jsonb_agg(name)
from the_table t2
where t2.name <> t1.name
and t2.notes -> 'link_to' ? t1.name) as referrals_from
from the_table t1;
| name | notes | referrals_to | referrals_from |
|---|---|---|---|
| anna | {"link_to": ["bob"]} | ["bob"] | ["bob"] |
| bob | {"link_to": ["anna", "claudia"]} | ["anna", "claudia"] | ["anna"] |
| claudia | {"link_to": []} | [] | ["bob"] |