1

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?

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.