HasT HasT - 4 months ago 9
SQL Question

Select data from two columns as array sorting in one column and grouping in another column

I have table like:

CREATE TABLE IF NOT EXISTS "points" (
"id" INTEGER NOT NULL,
"lat" DOUBLE PRECISION NOT NULL,
"lon" DOUBLE PRECISION NOT NULL
);

INSERT INTO "points" ("id", "lat", "lon") VALUES
(1,51.11,-0.11),
(2,51.12,-0.12),
(3,51.13,-0.13),
(4,51.14,-0.14),
(5,52.44,-2.44),
(6,52.45,-2.45),
(7,52.46,-2.46),
(8,52.47,-2.47);


How select data from two columns (
lat
,
lon
) as JSON array from table with sorting in one column (
id
)?
The result should be:

[
[51.11,-0.11],[51.12,-0.12],
[51.13,-0.13],[51.14,-0.14],
[44,-2.44],[52.45,-2.45],
[52.46,-2.46],[52.47,-2.47]
]

Answer
select to_json(array_agg(array[lat,lon] order by id))
from points
;
                                                      to_json                                                      
-------------------------------------------------------------------------------------------------------------------
 [[51.11,-0.11],[51.12,-0.12],[51.13,-0.13],[51.14,-0.14],[52.44,-2.44],[52.45,-2.45],[52.46,-2.46],[52.47,-2.47]]
Comments