create table site_order (
	id bigint,
	status int,
	phone text
);
create table site_order_item (
	id bigint,
	order_id bigint,
	name text,
	amount int,
	cost real
);
insert into site_order values
	(1,1,'+7 (000) 000-00-01'),
	(2,2,'+7 (000) 000-00-02');
insert into site_order_item values
	(1,1,'Водка',1,130.00),
	(2,1,'Туалетная бумага',2,60.00),
	(3,2,'Пиво',5,20.00),
	(4,2,'Чипсы',2,50.00);

with o as (
	select id,status,phone
	from site_order 
	where id in (1,2)
), i as (
	select t.order_id,json_agg(row_to_json(t)) as items
	from (
		select order_id,name,amount,cost
		from site_order_item
		where order_id in (select id from o)
	) as t
	group by t.order_id
)
select json_agg(row_to_json(t)) from (
	select o.*,i.items from o left join i on (i.order_id = o.id)
) as t

[
  {
    "id": 1,
    "status": 1,
    "phone": "+7 (000) 000-00-01",
    "items": [
      {"order_id": 1, "name": "Водка", "amount": 1, "cost": 130},
      {"order_id": 1, "name": "Туалетная бумага", "amount": 2, "cost": 60}
    ]
  },
  {
    "id": 2,
    "status": 2,
    "phone": "+7 (000) 000-00-02",
    "items": [
      {"order_id": 2, "name": "Пиво", "amount": 5, "cost": 20},
      {"order_id": 2, "name": "Чипсы", "amount": 2, "cost": 50}
    ]
  }
]

select t.order_id, json_agg(row_to_json(t)) as items
from (
	select order_id,name,amount,cost
	from site_order_item
	where order_id in (select id from o)
) as t
group by t.order_id