PostgreSQL json_build_object вложенный

Перво-наперво: я использую PostgreSQL 11.6, скомпилированный в Visual C ++ build 1800, 64-битный. :)

Я пытаюсь создать объект JSON непосредственно из базы данных.

Мой желаемый результат

{
   "1": [],
   "2": [],
   "3": []
}

Представьте, что мои столы похожи на:

MyIdTable
_id_|__key__
  1     test1
  2     test2
  3     test3
MyKeyValueTable

__id__|__fkidmyidtable__|__value__
  1           1             test
  2           1             test1
  3           2             test2
  4           2             test3

Теперь я создаю запрос

 select
        json_build_object(
            a.id,
            json_agg(
                b.*
            )
        )
          from "MyIdTable" a 
            inner join "MyKeyValueTable" b on a.id = b.fkidmyidtable group by a.id

В результате я получу несколько строк с желаемым результатом:

row 1: { 
         "1": [{ "id": 1, "fkidmyidtable": 1, "value": "test" }, { "id": 2, "fkidmyidtable": 1, "value": "test1" }]
       }
row 2: { 
         "2": [{ "id": 3, "fkidmyidtable": 2, "value": "test2" }, { "id": 4, "fkidmyidtable": 2, "value": "test3" }]
       }

После этого я могу использовать json_agg () для создания почти желаемого результата. Проблема в том, что это создаст

[ { "json_build_object": {"1": [{ "id": 1, "fkidmyidtable": 1, "value": "test" }, { "id": 2, "fkidmyidtable": 1, "value": "test1" }]}, "json_build_object": { "2": [{ "id": 3, "fkidmyidtable": 2, "value": "test2" }, { "id": 4, "fkidmyidtable": 2, "value": "test3" }] }]

Я хотел бы знать, возможно ли написать запрос для объединения моего созданного объекта в один объект json, например:

{
 "1": [{ "id": 1, "fkidmyidtable": 1, "value": "test" }, { "id": 2, "fkidmyidtable": 1, "value": "test1" }],
 "2": [{ "id": 3, "fkidmyidtable": 2, "value": "test2" }, { "id": 4, "fkidmyidtable": 2, "value": "test3" }]
}

Большое спасибо заранее, что нашли время, чтобы прочитать :)!

Всего 1 ответ


Если я следил за вами правильно, вы можете добавить другой уровень агрегации и использовать json_object_agg() :

select json_object_agg(id, js) res
from (
    select a.id, json_agg(b.*) js
    from "MyIdTable" a 
    inner join "MyKeyValueTable" b on a.id = b.fkidmyidtable 
    group by a.id
) t

Есть идеи?

10000