SQLize Online / PHPize Online  /  SQLtest Online

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;