PostgreSQL: Aggregate multiple rows as JSON array based on specific column

Question

I have a table data_tracks containing points (latitude, longitude), recorded at a specific time (created_at) and belonging to a trip (trip_log_id). A trip usually has at least two points (start point and end point).

table: data_tracks

• created_at : text
• latitude : float
• longitude : float
• trip_log_id : integer

I would like to generate a JSON output, consisting of arrays of arrays, whereas each of the inner arrays contains the aggregated points of a trip (as indicated by trip_log_id).

Example consisting of two trips:

[

  [
    {
      "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"
    }
  ]

]

Based on the suggestion of another post [1] I was able to generate an output really close to what I am looking for. However, the ouput is interpreted as text, which introduces undesired escape characters. Furthermore, the outer array is missing.

The query should work on PostgreSQL 9.3.

Adapted Query:

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; 

Output:

{

    "[
        {
            \"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

Disclaimer: Only for Postgres 9.4+ 9.4 and further version add huge support for JSON to Postgres. 9.3 is already unsupported. You should really upgrade your database.

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 creates your main json objects
2. json_agg() ... GROUP BY trip_log_id groups these json objects into one trip object
3. second json_agg aggregates all trips into one array

---

demo:SQL Fiddle

Version for 9.3 (strictly not recommended!)

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