Как в PostgreSql сделать запрос с двойной вложенностью json в результате?

Question

[SlavaMorg]

Допустим, у нас есть таблица с таким содержанием:
house_id, doc_id, doc_name, file_id, file_path
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/"

В сущности, это не таблица, а результат запроса, который объединил три таблицы: houses, document, files. Есть несколько домов. На каждый дом может быть несколько документов. Каждый документ может включать в себя несколько файлов.

Для простоты представим, что всё таки таблица, или пусть view. Какой запрос нужно написать (БД - Postrge), чтобы получить такой результат:
house_id, json_result
18, {{"doc_id": 1, "doc_name": "Приказ", "files": {{"file_id": "36", "file_path": "/root/1234/"}}}, {"doc_id": 3, "doc_name": "Поручение", "files": {{"file_id": "38", "file_path": "/root/9101112/"}}}}
20, {{"doc_id": 2, "doc_name": "Уведомление", "files": {{"file_id": "37", "file_path": "/root/5678/"}}}, {"doc_id": 4, "doc_name": "Разрешение", "files": {{"file_id": "39", "file_path": "/root/13141516/"}, {"file_id": "40", "file_path": "/root/17181920/"}}}}

Т е сначала сгруппировать файлы по документам, потом документы по домам. Я понимаю, что нужно использовать функции типа row_to_json, json_build_object и подобные, но как именно это реализовать, ума не приложу.

Comments

[Дмитрий]

json_agg?

Answer 1

[Slava Rozhnev]

Примерно так:

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;

Сначала групприруешь по двум параметрам, потом ещё раз по одному

https://sqlize.online/sql/psql15/84856c35bf40811de...