How to combine multiple columns into one in postgresql using row_to_json

Question

I have table with 3 columns:

id|name|geometry
-----------------
1  |xie|geometry

i want to get the geometry in geojson format.

select id,name,ST_AsGEOJSON(geom) from table;
`o/p : `'{"id":"1","name":"xie","type":"Polygon","coordinates"[[[103.349002218076,-4.79402531349065]]]}'`

then i am using row_to_json to combine the columns:

SELECT row_to_json(t) from (SELECT id,name,ST_AsGEOJSON(geom) from table where id=1)t

this command is changing the original output as:

o/p :`'{"id":"1","name":"xie","st_asgeojson":"{\"type\":\"Polygon\",\"coordinates\":[[[102.325742539781,-3.83353755877152]]]}"}'`

but i want to combine the columns without any change in the original output.How can i do that?

But how do you want escape the quotes? Add a example of your expected result — Tom-db
– Tom-db, Commented Oct 2, 2015 at 7:22

Tom-db · Accepted Answer · 2015-10-02 14:48:12Z

1

You have two choices:

1) You build a json with the geometry as geojson but expressed as string (without quotes):

SELECT row_to_json(t) FROM (
    SELECT id,name,replace(ST_AsGEOJSON(geom),'"','') AS geom 
    FROM table where id=1
) AS t;

Result:

{"id":1,"name":"xie","geom":"{type:Polygon,coordinates:[[[0,0],[0,1],[1,1],[1,0],[0,0]]]}"}

2) Build a json with a legal geojson geometry. Appending a json to another is unfortunately not really supported (yet) by Postgres. You have to build the Json manually:

 SELECT (format(
  '{"id":%s,"name":%s,"geom":%s}', to_json(id), to_json(name),st_asgeojson(geom))
)::json     from table where id=1;

Result:

{"id":1,"name":"xie","geom":{"type":"Polygon","coordinates":[[[0,0],[0,1],[1,1],[1,0],[0,0]]]}}

edited Oct 2, 2015 at 14:48

answered Oct 2, 2015 at 8:01

Tom-db

6,9584 gold badges33 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

How to combine multiple columns into one in postgresql using row_to_json

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related