ARRAY_AGG interrupted by GROUP BY when trying to ORDER BY timestamps

Question

I have prepared an SQL Fiddle for my problem -

Given the following table:

CREATE TABLE chat(
    gid integer,            /* game id */
    uid integer,            /* user id */
    created timestamptz,
    msg text
);

filled with the following test data:

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');

I can use the following query:

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;

for fetching the table records as a JSON object with "gid" as keys and array-values:

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

However I am missing a minor thing and just can't figure it out:

I need to order the arrays by "created".

So I add ORDER BY created ASC to the above query and also have to add GROUP BY gid, created (you can see the problematic code as commented out in the above query).

This breaks however the array_agg and results in 1-element arrays (and overwrites duplicated "gid" keys, when returned as jsonb from my custom stored function):

{ "10": [{ "uid":2, "created":1514889800, "msg":"msg 6" }],

  "20": [{ "uid":4, "created":1514889980, "msg":"msg 9" }] }

Is ordering even possible here or should I resort to doing it in my JAVA application?

score 5 · Accepted Answer · answered Jan 02 '18 at 10:53

Specify the ORDER BY in the array_agg call:

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
      ) ORDER BY created -- Order the elements in the resulting array
    ) AS y
  FROM chat 
  GROUP BY gid
) x;

returns (formatted using jq):

{
  "10": [
    {
      "uid": 1,
      "created": 1514890093,
      "msg": "msg 1"
    },
    {
      "uid": 2,
      "created": 1514890153,
      "msg": "msg 2"
    },
    {
      "uid": 1,
      "created": 1514890213,
      "msg": "msg 3"
    },
    {
      "uid": 2,
      "created": 1514890273,
      "msg": "msg 4"
    },
    {
      "uid": 1,
      "created": 1514890333,
      "msg": "msg 5"
    },
    {
      "uid": 2,
      "created": 1514890393,
      "msg": "msg 6"
    }
  ],
  "20": [
    {
      "uid": 3,
      "created": 1514890453,
      "msg": "msg 7"
    },
    {
      "uid": 4,
      "created": 1514890513,
      "msg": "msg 8"
    },
    {
      "uid": 4,
      "created": 1514890573,
      "msg": "msg 9"
    }
  ]
}

I didn't realize that [ORDER BY](https://www.postgresql.org/docs/9.6/static/functions-aggregate.html) can be used with aggregate functions, thank you! — Alexander Farber, Jan 02 '18 at 12:33

ARRAY_AGG interrupted by GROUP BY when trying to ORDER BY timestamps

1 Answers1