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

}

S-Man · Accepted Answer · 2018-10-31 15:47:37Z

33

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

json_build_object creates your main json objects
json_agg() ... GROUP BY trip_log_id groups these json objects into one trip object
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

edited Oct 31, 2018 at 15:47

answered Oct 31, 2018 at 15:31

S-Man

24k9 gold badges51 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Patrick Over a year ago

Thanks, you saved my day! Sorry for not providing a fiddle, would have saved you time. I could successfully test it on my local dev environment (Postgres 9.5); I will follow your suggestion to upgrade production env to 9.5, too.

S-Man Over a year ago

Added a version for 9.3 already. Nevertheless why are you updating to another old version. I would recommend to as least 9.6 (stable is 11 right now!)

Patrick Over a year ago

I was just thinking that making the smallest necessary change would be more safe, as we are using jruby-pg and it doesn't explicity state to be compatible with Postgres 11. The version they used for testing is 9.3.6.

S-Man Over a year ago

I don't know your tool. But IMHO it absolute safe to switch to 9.6. IIRC some specialized details changed at version 10, so this should be tested a little harder. But in normal cases there are no reasons to stay old in the PostgreSQL world. Changes are exactly decribed in their changelogs. Read them carefully if you're not sure.

Brain90 Over a year ago

thanks for break down the hierarchy steps. you saved my day!

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related