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 :)!
Advertisement
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