select columns into a json from postgres

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!

Ashish Padakannaya · Accepted Answer · 2016-02-11T11:05:30.870

13

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 '16 at 11:05

answered Feb 02 '16 at 17:43

Ashish Padakannaya

463
2
7
17

2

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... – overdawn Feb 02 '16 at 18:36
@overdawn You're right. The concatenation is useless .answer updated – Ashish Padakannaya Feb 04 '16 at 05:15

score 11 · Answer 2 · answered Feb 02 '16 at 13:55

11

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

answered Feb 02 '16 at 13:55

Dmitry Umarov

426
3
5

score 2 · Answer 3 · edited May 05 '22 at 02:27

2

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 05 '22 at 02:27

Erwin Brandstetter

605,456
145
1,078
1,228

answered Feb 02 '16 at 13:56

overdawn

321
3
10

select columns into a json from postgres

3 Answers3