PostgreSQL json_build_object nested

First things first: I’m using PostgreSQL 11.6, compiled by Visual C++ build 1800, 64-bit. 🙂

Im trying to create a JSON object directly from the database.

My desired result is

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

Imagine my tables like:

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

Now I create a query

 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

This will get me as result, multiple rows with the desired result:

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" }]
       }

After this I can use json_agg() to create almost my desired result. The issue is that it will create

[ { "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" }] }]

I would like to know if its possible to write a query to merge my created object into one json object like:

{
 "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" }]
}

Thank you very much in advance for taking the time to read :)!

Answer

If I followed you correctly, you can add another level of aggregation and use 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