json_agg with dynamic 0-level key

Question

I'm migrating a schema from NoSQL to postgres. Today's object "b" exists in NoSQL as an element of object a & looks in source like this...

{
 "a": {
 "id": 1,
 "b":{
 "c1": {
 "d1": 1,
 "d2": 2
 },
 "c2": {
 "d1": 1,
 "d2": 2
 },
 "c3": {
 "d1": 3,
 "d2": 4
 }
 }
 }
}

...which I've tentatively assigned to a schema resembling the below ERD

+---+ +-----+ +---+ +---+
| b |<--<| b_d |>-->| d |>-->| c |
+---+ +-----+ +---+ +---+

full db_fiddle

I'd like to be able to recompose the normalised data into the original object but I've been unable to do so. Instead of getting "c" objects as direct key-value pairs, I'm getting whole objects. For example...

select 
 b_d.b_id, 
 json_agg(json_build_object(c.name,json_build_object('d1',d.d1,'d2',d.d2))) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

...returns...

[{
 "c1 ": {
 "d1": 1,
 "d2": 2
 }
},
{
 "c2 ": {
 "d1": 1,
 "d2": 2
 }
},
{
 "c3 ": {
 "d1": 3,
 "d2": 4
 }
}]

...which you will note has an additional nesting level versus the source object. I suspect I've gone wrong out of the gate with json_build_object but so far have been unable to successfully reconstruct the "b" object as it appears in the original json document.

What do I modify about the query and/or the underlying schema to normalize and denormalize this object?

Answer 1

Instead of looking on the json functions docs page, look instead on the aggregate functions docs page. jsonb_object_agg() will aggregate the object without an additional level of nesting as seen in this demo.

select 
 b_d.b_id,
 jsonb_object_agg(c.name,json_build_object('d1',d.d1,'d2',d.d2)) as b
from b_d
join d on d.id = b_d.d_id
join c on c.id = d.c_id
group by b_d.b_id;

...returns the original object as desired.

{
 "c1 ": {
 "d1": 1,
 "d2": 2
 },
 "c2 ": {
 "d1": 1,
 "d2": 2
 },
 "c3 ": {
 "d1": 3,
 "d2": 4
 }
}

See also