複雜的 JSON 查詢

我有一張這樣的桌子：

id | lat | lon | date       | speed
1  | 45  | 66  | 2018-02-01 | 139
1  | 44  | 63  | 2018-04-01 | 129
2  | 41  | 56  | 2018-01-01 | 109
2  | 44  | 45  | 2018-04-01 | 99

我想做一個這樣的JSON：

{1:{path:[[45,66],[44,63]],speed:129}, 2:{path:[[41,56],[44,45]], speed:99}};

在最後日期採取的速度；

沒有速度參數，解決方案是

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; 

如何修改它以包括最後速度？

• PostgreSQL 9.6

工作一個，可能有更好的東西。

SELECT json_agg(json_build_object(id, to_jsonb(t) - 'id'))
FROM (
WITH last_time AS (SELECT DISTINCT ON (id) id, time_id, speed FROM foo ORDER BY id, time_id DESC),
            t AS (SELECT id, jsonb_agg(jsonb_build_array(latitude,longitude)) AS path FROM foo GROUP BY id)
            SELECT t.id as id, t.path as path, last_time.speed as speed FROM t LEFT JOIN last_time ON t.id=last_time.id
) AS t;

像這樣的東西應該​​工作，

SELECT t1.id, ST_AsGeoJSON(line), t2.speed;
FROM (
   SELECT id,
     ST_MakeLine( ST_MakePoint(lon,lat)::geography ORDER BY date ) AS line
   FROM tbl
   GROUP BY id
) AS t1
CROSS JOIN LATERAL (
 SELECT speed
 FROM tbl AS t2
 WHERE t2.id = t1.id
 ORDER BY date DESC
 FETCH FIRST ROW ONLY
) AS t2;

ST_MakePoint(lon,lat)如果您儲存在表上而不是單獨的列中，則工作速度會更快。

注意ST_AsGeoJSON只實現了 geojson 規範的幾何部分。你必須完成文件。速度會是一個properties下文件。

引用自：https://dba.stackexchange.com/questions/226985