vishal vishal - 1 year ago 69
JSON Question

Is there any way to fetch data in json format from PostgreSQL table which has different field not the json data type field?

I have a table in PostgreSQL which has field like id,name,city,mob
etc. i got that in PostgreSQL there is json data type in which we can
store the data in json format but I don't want to use json data type.

I want to fetch these table field (id,name,city,mob) which are normal fields not json data type field that I want in json format

I want that suppose mytable is a table which has id,name,city,mob,dob as field and

now i want to execute query like:

select name,city from mytable where id=5;

This query should return output like

so how can i do that and what should be the query for that?

Answer Source

Use row_to_json():

select row_to_json(t) 
from (
   select name,city from mytable 
   where id=5
) t

The derived table is necessary to create a record type with proper names for the columns. row_to_json((name,city)) would not achieve this.


If you want all columns from the table (not just two as shown in the question) then you don't need the derived table:

select row_to_json(mytable)
from mytable
where id = 5;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download