title: PostgreSQL: Aggregate multiple rows as JSON array based on specific column
body: <p>I have a table <em>data_tracks</em> containing points (<strong>latitude</strong>, <strong>longitude</strong>), recorded at a specific time (<strong>created_at</strong>) and belonging to a trip (<strong>trip_log_id</strong>). A trip usually has at least two points (start point and end point).</p>
  
  <p>table: <strong>data_tracks</strong></p>
  
  <ul>
  <li><code>created_at : text</code></li>
  <li><code>latitude : float</code></li>
  <li><code>longitude : float</code></li>
  <li><code>trip_log_id : integer</code></li>
  </ul>
  
  <p>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).</p>
  
  <p>Example consisting of two trips:</p>
  
  <pre><code>[
  
    [
      {
        "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"
      }
    ]
  
  ]
  </code></pre>
  
  <p>Based on the suggestion of another post <a href="https://dba.stackexchange.com/a/102087/164026">[1]</a> 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.</p>
  
  <p>The query should work on PostgreSQL 9.3.</p>
  
  <p>Adapted Query:</p>
  
  <pre><code>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; 
  </code></pre>
  
  <p>Output: </p>
  
  <pre><code>{
  
      "[
          {
              \"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\"
          }
      ]"
  
  }
  </code></pre>