JSON Query in PostgreSQL

Question

I have a table:

CREATE TABLE foo (id,lat,lon)
AS VALUES
  ( 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]]}

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 — Evan Carroll, 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. — Evan Carroll, Jan 11, 2019 at 23:44

Evan Carroll · Accepted Answer · 2019-01-11 23:28:15Z

First you do something like this,

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

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)
FROM (
  SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
  FROM foo
  GROUP BY id
) AS t;

1 Answer 1