create table tbl (
house_id int, doc_id int, doc_name text, file_id int, file_path text
);
insert into tbl values
(18, 1, 'ΠΡΠΈΠΊΠ°Π·', 36, '/root/1234/'),
(20, 2, 'Π£Π²Π΅Π΄ΠΎΠΌΠ»Π΅Π½ΠΈΠ΅', 37, '/root/5678/'),
(18, 3, 'ΠΠΎΡΡΡΠ΅Π½ΠΈΠ΅', 38, '/root/9101112/'),
(20, 4, 'Π Π°Π·ΡΠ΅ΡΠ΅Π½ΠΈΠ΅', 39, '/root/13141516/'),
(20, 4, 'Π Π°Π·ΡΠ΅ΡΠ΅Π½ΠΈΠ΅', 40, '/root/17181920/');
with a as (
select
house_id,
doc_id,
json_agg(json_build_object('doc_name', doc_name, 'file_id', file_id, 'file_path', file_path)) j
from tbl
group by house_id, doc_id
) select house_id, json_agg(j)
from a
group by house_id;
Telegram: DB developers channel