Postgresql: Get columns of other table as array in a object as a column with condition

i have 2 tables, servers and virtual_machines. I want to see the virtual_machines as array in the servers where serverName equals the servers name

Like this

+--------------------------------------------------------------------------------------------+
|ID | Name    | virtual_machines                                    
+--------------------------------------------------------------------------------------------+
| 1 | Server1 | [{id:1,name:'vm1',serverName:'Server1'},{id:2,name:'vm2',serverName:'Server1'}]  

I have servers tables

+-------------------------------------+
|ID | Name | 
+-------------------------------------+

and virtual_machines tables

+-------------------------------------+
|ID | Name | serverName
+-------------------------------------+

I tried many options like bellow but i cant make object array in colum and postgre always add key into object.

SELECT srv.*
     , json_build_object('VM', vm.*)
FROM servers srv, virtual_machines vm WHERE vm.serverName = srv.Name

Thanks for any help

Answer

You can use array_agg() to have both the virtual_machines in one row.

SELECT srv.id,srv.name
     ,array_agg( json_build_object('VM', vm.*))
FROM servers srv, virtual_machines vm WHERE vm.serverName = srv.Name
group by srv.id,srv.name



 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');

Query:

 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

Output:

id	name	virtual_machines
1	Server1	[{id: 2, name: vm2, serverName: Server1}, {id: 1, name: vm1, serverName: Server1}]

db<fiddle here

With services table :

 create table servers(ID int, Name varchar(50));
 
 create table virtual_machines (ID int, Name varchar(50), serverName varchar(50));
 create table Services (ID int, Name varchar(50), vmName varchar(50));
 
 insert into servers values( 1 , 'Server1');
 insert into virtual_machines values(1,'vm1','Server1');
 insert into virtual_machines values(2,'vm2','Server1');
 
 insert into Services values(1,'service1','vm1');

Query:

 with vm_services as
 (
     SELECT vm.id,vm.name, vm.servername
          ,jsonb_agg    ( json_build_object('id', s.id, 'name', s.name, 'vmName', s.vmname))Services
     FROM virtual_machines vm left join services s on vm.name = s.vmname
     group by vm.id,vm.name,vm.servername
     order by vm.id
 )
 SELECT srv.id,srv.name
      ,jsonb_agg    ( json_build_object('id', vm.id, 'name', vm.name, 'serverName', vm.serverName,'Services',vm.services))virtual_machines
 FROM servers srv inner join vm_services vm on vm.serverName = srv.Name
 group by srv.id,srv.name

Output:

id	name	virtual_machines
1	Server1	[{id: 2, name: vm2, Services: [{id: null, name: null, vmName: null}], serverName: Server1}, {id: 1, name: vm1, Services: [{id: 1, name: service1, vmName: vm1}], serverName: Server1}]

db<fiddle here