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 servers(ID int, Name varchar(50));
create table virtual_machines (ID int, Name varchar(50), serverName varchar(50));
insert into servers values( 1 , 'Server1');
insert into virtual_machines values(1,'vm1','Server1');
insert into virtual_machines values(2,'vm2','Server1');
1 rows affected
1 rows affected
1 rows affected
SELECT srv.id,srv.name
,jsonb_agg ( json_build_object('id', vm.id, 'name', vm.name, 'serverName', vm.serverName))virtual_machines
FROM servers srv, virtual_machines vm WHERE vm.serverName = srv.Name
group by srv.id,srv.name
| id | name | virtual_machines |
|---|---|---|
| 1 | Server1 | [{"id": 2, "name": "vm2", "serverName": "Server1"}, {"id": 1, "name": "vm1", "serverName": "Server1"}] |