Search code examples
postgresqlpostgresql-11

Postgres create a nested json object from two json objects

I have formed two json objects with 2 separate queries as below.

Query 1: select json_build_object('v', 'v1', 'vname', 'vname1','user', 'user1') which gives the below json object:

{"v" : "v1", "vname" : "vname1", "user" : "user1"}

Query 2: select json_build_object('id', 'is1','des' , 'des1','trg' , 'trg1','stat' , 'stat1') which gives below json object:

{"id" : "is1", "des" : "des1", "trg" : "trg1", "stat" : "stat1"}

My requirement is I want to create a single json using the above 2 json objects as below:

{"t1":{"v" : "v1", "vname" : "vname1", "user" : "user1"},"t2":{"id" : "is1", "des" : "des1", "trg" : "trg1", "stat" : "stat1"}}

I have tried json_build_object with the above queries as inline queries but it doesn't work.

Thank you very much in advance.

Solution

  • Nested calls to jon_build_object are absolutely supported in postgres.

    Here's one that produces the output that you're looking for using the two statements that you have in your question.

    select json_build_object(
    't1', json_build_object('v', 'v1', 'vname', 'vname1','user', 'user1'),
    't2', json_build_object('id', 'is1','des' , 'des1','trg' , 'trg1','stat' , 'stat1')
);

json_build_object
------------------
 {"t1" : {"v" : "v1", "vname" : "vname1", "user" : "user1"}, "t2" : {"id" : "is1", "des" : "des1", "trg" : "trg1", "stat" : "stat1"}}