select columns into a json from postgres [duplicate]

Question

i have a table employee like this

name     value 
'e1'     'rahul'
'e2'     'priya'
'e3'     'abhijit'

and i need to extract json from the two columns such that the result is like this

{'e1':'rahul','e2':'priya','e3':'abhijit'}

I've tried the following query and the results is as follows

select row_to_json((name,value)) from employee 

O/P

{'f1':'e1','f2':'rahul'}
{'f2':'e1','f2':'priya'}
{'f3':'e1','f2':'abhijit'}

i don't want the f1 and f2 names over there,please advice!

APA · Accepted Answer · 2016-02-11 11:05:30Z

18

thanks for the inputs guys but i just found out that i had to upgrade postgresql from 9.3 to 9.4 to get the function json_build_object .it gets the job done with a few changes to the query structure.

select json_agg(json_build_object(name,value)) from employee;

[{'e1':'rahul','e2':'priya','e3':'abhijit'}]

edited Feb 11, 2016 at 11:05

answered Feb 2, 2016 at 17:43

APA

5732 gold badges7 silver badges17 bronze badges

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

2 Comments

overdawn Over a year ago

what is the point in concatenation with empty string? It seems you could just cast explicitly name::text, unless the type of that column is not text already...

APA Over a year ago

@overdawn You're right. The concatenation is useless .answer updated

Dmitry Umarov · Accepted Answer · 2016-02-02 13:55:43Z

16

SELECT ROW_TO_JSON(a) FROM (SELECT name, value FROM employee) a;

answered Feb 2, 2016 at 13:55

Dmitry Umarov

4763 silver badges5 bronze badges

Comments

Erwin Brandstetter · Accepted Answer · 2022-05-05 02:27:00Z

4

If I get your question correctly, then, as mentioned here, in Postgres 9.4+ you might use json_build_object():

create temp table employee as (select 'key'::text as name, 'val'::text as value);
select json_build_object(name,value) from employee;

Here is the result:

json_build_object
-------------------
 {"key" : "val"}

edited May 5, 2022 at 2:27

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Feb 2, 2016 at 13:56

overdawn

3911 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

select columns into a json from postgres [duplicate]

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related