vishal vishal - 4 months ago 8
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
{"name":"xyz","city":"abc"}


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

Answer

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.

Edit:

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;
Comments