Sample Header Ad - 728x90

json_agg with dynamic 0-level key

1 vote
1 answer
968 views
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 |-->| 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?
Asked by Peter Vandivier (5728 rep)
Nov 18, 2019, 01:51 PM
Last activity: Nov 21, 2019, 01:09 PM