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 |
+---+ +-----+ +---+ +---+
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?