I have a table:

CREATE TABLE foo (id,lat,lon)
  ( 1, 34, 45 ),
  ( 1, 45, 56 ),
  ( 2, 56, 67 ),
  ( 2, 58, 64 );

How can I get JSON like that:

{"1":[[34,45],[45,56]], "2":[[56,67],[58,64]]}
  • This has nothing to do with spatial, or geojson. Jan 11, 2019 at 23:21
  • The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS's ST_ToGeoJSON Jan 11, 2019 at 23:29
  • I have an ability to save them like Postgis geom::point, can u provide this solution also?
    – delkov
    Jan 11, 2019 at 23:36
  • no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS. Jan 11, 2019 at 23:44

1 Answer 1


First you do something like this,

SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo

That aggregates the values into an JSON Array. You get

 id |          j           
  2 | [[56, 67], [58, 64]]
  1 | [[34, 45], [45, 56]]

From there you need to build an JSON Object..

SELECT jsonb_object_agg(id,j)
  SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
  FROM foo
) AS t;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.