title: ARRAY_AGG interrupted by GROUP BY when trying to ORDER BY timestamps
body: <p>I have prepared an <a href="http://sqlfiddle.com/#!17/2c9c5/49" rel="nofollow noreferrer">SQL Fiddle</a> for my problem -</p>
  
  <p>Given the following table:</p>
  
  <pre><code>CREATE TABLE chat(
      gid integer,            /* game id */
      uid integer,            /* user id */
      created timestamptz,
      msg text
  );
  </code></pre>
  
  <p>filled with the following test data:</p>
  
  <pre><code>INSERT INTO chat(gid, uid, created, msg) VALUES
      (10, 1, NOW() + interval '1 min', 'msg 1'),
      (10, 2, NOW() + interval '2 min', 'msg 2'),
      (10, 1, NOW() + interval '3 min', 'msg 3'),
      (10, 2, NOW() + interval '4 min', 'msg 4'),
      (10, 1, NOW() + interval '5 min', 'msg 5'),
      (10, 2, NOW() + interval '6 min', 'msg 6'),
      (20, 3, NOW() + interval '7 min', 'msg 7'),
      (20, 4, NOW() + interval '8 min', 'msg 8'),
      (20, 4, NOW() + interval '9 min', 'msg 9');
  </code></pre>
  
  <p>I can use the following query:</p>
  
  <pre><code>SELECT json_object_agg(
            gid, array_to_json(y)
         ) FROM (
         SELECT  gid,
            array_agg(
              json_build_object(
                'uid', uid,
                'created', EXTRACT(EPOCH FROM created)::int,
                'msg', msg)
            ) y
          FROM  chat 
          GROUP BY gid /*, created
          ORDER BY created ASC */
  ) x;
  </code></pre>
  
  <p>for fetching the table records as a JSON object with "gid" as keys and array-values:</p>
  
  <pre><code>{ "20" : [
      {"uid" : 3, "created" : 1514889490, "msg" : "msg 7"},
      {"uid" : 4, "created" : 1514889550, "msg" : "msg 8"},
      {"uid" : 4, "created" : 1514889610, "msg" : "msg 9"}
  ], 
  
  "10" : [
      {"uid" : 1, "created" : 1514889130, "msg" : "msg 1"},
      {"uid" : 2, "created" : 1514889190, "msg" : "msg 2"},
      {"uid" : 1, "created" : 1514889250, "msg" : "msg 3"},
      {"uid" : 2, "created" : 1514889310, "msg" : "msg 4"},
      {"uid" : 1, "created" : 1514889370, "msg" : "msg 5"},
      {"uid" : 2, "created" : 1514889430, "msg" : "msg 6"}
  ] }
  </code></pre>
  
  <p>However I am missing a minor thing and just can't figure it out:</p>
  
  <p>I need to order the arrays by "created".</p>
  
  <p>So I add <code>ORDER BY created ASC</code> to the above query and also have to add <code>GROUP BY gid, created</code> (you can see the problematic code as commented out in the above query).</p>
  
  <p>This <em>breaks</em> however the <code>array_agg</code> and results in 1-element arrays (and overwrites duplicated "gid" keys, when returned as <code>jsonb</code> from my custom stored function):</p>
  
  <pre><code>{ "10": [{ "uid":2, "created":1514889800, "msg":"msg 6" }],
  
    "20": [{ "uid":4, "created":1514889980, "msg":"msg 9" }] }
  </code></pre>
  
  <p>Is ordering even possible here or should I resort to doing it in my JAVA application?</p>