sql - PostgreSQL:根据特定列将多行聚合为 JSON 数组

Question

我有一个包含点(纬度、经度)的表data_tracks，记录在特定时间(created_at) 并且属于旅行 (trip_log_id)。一次旅行通常至少有两个点(起点和终点)。

表:data_tracks

• created_at : text
• 纬度: float
• 经度: float
• trip_log_id : integer

我想生成一个 JSON 输出，由数组的数组组成，而每个内部数组都包含旅行的聚合点(如 trip_log_id 所示)。

包含两个行程的示例:

[

  [
    {
      "latitude": 52.504873,
      "longitude": 13.396681,
      "recorded_at": "2018-10-11T15:16:32.775"
    },
    {
      "latitude": 52.505225,
      "longitude": 13.396976,
      "recorded_at": "2018-10-11T15:16:32.807"
    },
    {
      "latitude": 52.505372,
      "longitude": 13.397035,
      "recorded_at": "2018-10-11T15:16:32.835"
    },
  ],

  [
    {
      "latitude": 52.5242370846803,
      "longitude": 13.3443558528637,
      "recorded_at": "2018-10-11T11:22:54.666"
    },
    {
      "latitude": 52.5242366166393,
      "longitude": 13.3443558656828,
      "recorded_at": "2018-10-11T11:22:54.727"
    }
  ]

]

根据另一篇文章的建议[1]我能够生成非常接近我正在寻找的输出。但是，输出被解释为文本，这引入了不需要的转义字符。此外，缺少外部数组。

查询应该适用于 PostgreSQL 9.3。

改编查询:

SELECT Array_agg(rw) 
FROM  (SELECT trip_log_id, 
              (SELECT To_json(Array_agg(Row_to_json(t))) 
               FROM   (SELECT latitude, 
                              longitude, 
                              created_at AS recorded_at 
                       FROM   public.data_tracks 
                       WHERE  trip_log_id = b.trip_log_id) t) rw 
       FROM   data_tracks b 
       GROUP  BY trip_log_id) z; 

输出:

{

    "[
        {
            \"latitude\":52.504873,
            \"longitude\":13.396681,
            \"recorded_at\":\"2018-10-11T15:16:32.775\"
        },
        {
            \"latitude\":52.505225,
            \"longitude\":13.396976,
            \"recorded_at\":\"2018-10-11T15:16:32.807\"
        },
        {
            \"latitude\":52.505372,
            \"longitude\":13.397035,
            \"recorded_at\":\"2018-10-11T15:16:32.835\"
        },
        {
            \"latitude\":52.505686,
            \"longitude\":13.397218,
            \"recorded_at\":\"2018-10-11T15:16:32.871\"
        },
        {
            \"latitude\":52.505921,
            \"longitude\":13.397389,
            \"recorded_at\":\"2018-10-11T15:16:32.906\"
        },
        {
            \"latitude\":52.506166,
            \"longitude\":13.397593,
            \"recorded_at\":\"2018-10-11T15:16:32.936\"
        },
        {
            \"latitude\":52.50647,
            \"longitude\":13.397856,
            \"recorded_at\":\"2018-10-11T15:16:32.969\"
        },
        {
            \"latitude\":52.506786,
            \"longitude\":13.398065,
            \"recorded_at\":\"2018-10-11T15:16:33\"
        }
    ]",

    "[
        {
            \"latitude\":52.5242370846803,
            \"longitude\":13.3443558528637,
            \"recorded_at\":\"2018-10-11T11:22:54.666\"
        },
        {
            \"latitude\":52.5242366166393,
            \"longitude\":13.3443558656828,
            \"recorded_at\":\"2018-10-11T11:22:54.727\"
        }
    ]"

}

Answer 1

免责声明:仅适用于 Postgres 9.4+ 9.4 及更高版本为 Postgres 添加了对 JSON 的巨大支持。 9.3 已经不受支持。你真的应该升级你的数据库。

demo:db<>fiddle

SELECT json_agg(trips)
FROM (
    SELECT 
        json_agg(
            json_build_object(
                'recorded_at', created_at, 
                'latitude', latitude, 
                'longitude', longitude
            )
        ) as trips
    FROM data_tracks
    GROUP by trip_log_id
)s

1. json_build_object 创建您的主要 json 对象
2. json_agg() ... GROUP BY trip_log_id 将这些 json 对象组合成一个 trip 对象
3. 第二个 json_agg 将所有行程聚合到一个数组中

---

demo:SQL Fiddle

9.3 版本(严格不推荐!)

SELECT json_agg(trips)
FROM (
    SELECT 
        json_agg(
            ('{"recorded_at":"' || created_at || 
            '","latitude":' || latitude || 
            ',"longitude":' || longitude || '}')::json
        ) as trips
    FROM data_tracks
    GROUP by trip_log_id
)s