Postgresql – JSON Query in PostgreSQL

jsonpostgresql

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]]}

Best Answer

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;
Related Question