Make Column value as key using JSON function in Postgres

Question

I'm using Postgres database and have a table as below

Table Name: Test

id    firstname    lastname
 1     Sam          Crews
 2     John         Dave

I'm trying to get result set back in below JSON format but with no luck

Expected:

[{"1": {"firstname": "Sam", "lastname": "Crews"}},
 {"2": {"firstname": "John", "lastname": "Dave"}}
]

I tried using row_to_json, json_build_object functions but the output is bit different (as below).

Actual:

[{"id": "1", "firstname": "Sam", "lastname": "Crews"},
 {"id": "2", "firstname": "John", "lastname": "Dave"}
]

Any pointers on how to achieve expected result would be greatly appreciated.

Thank you!

user330315 · Accepted Answer · 2017-06-09 06:54:07Z

3

row_to_json() will always use all column names to create the keys in the JSON document. If you only want two columns, you need to explicitly specify them.

You also want a nested JSON object, so you need to nest the methods to create one:

select json_agg(json_build_object(
                 id, 
                 json_build_object('firstname', firstname, 'lastname', lastname))
                )
from the_table;

Klin made me realize that with Postgres 9.6 you can simplify the creation of the firstname/lastname object:

select json_agg(json_build_object(id, to_jsonb(the_table) - 'id'))
from the_table;

answered Jun 9, 2017 at 6:54

user330315

Sign up to request clarification or add additional context in comments.

Comments

klin · Accepted Answer · 2017-06-09 06:54:46Z

3

with my_table(id, firstname, lastname) as (
values
(1, 'Sam', 'Crews'),
(2, 'John', 'Dave')
)

select jsonb_agg(obj)
from my_table,
jsonb_build_object(id, to_jsonb(my_table)- 'id') obj

                                              jsonb_agg                                               
------------------------------------------------------------------------------------------------------
 [{"1": {"lastname": "Crews", "firstname": "Sam"}}, {"2": {"lastname": "Dave", "firstname": "John"}}]
(1 row)

answered Jun 9, 2017 at 6:54

klin

123k15 gold badges240 silver badges262 bronze badges

Collectives™ on Stack Overflow

Make Column value as key using JSON function in Postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related