当尝试按时间戳排序时，GROUP BY会打断ARRAY_AGG。

Question

当尝试按时间戳排序时，GROUP BY会打断ARRAY_AGG。

sqlpostgresqlsql-order-bypostgresql-jsonarray-agg

5

我已经为我的问题准备了一个SQL Fiddle -

给定以下表格：

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

填充以下测试数据：

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

我可以使用以下查询：

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;

用 "gid" 作为键和数组值，获取表记录的 JSON 对象：

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

然而我还缺少一个小东西，就是无法弄清楚：需要按“created”对数组进行排序。因此，我在上面的查询中添加了 ORDER BY created ASC，同时还必须添加 GROUP BY gid, created（您可以在上面的查询中看到有问题的代码作为注释）。这会导致 array_agg 出现问题，并导致结果为 1 元素数组（并覆盖重复的“gid”键，当从我的自定义存储函数返回为 jsonb 时）。

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

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

这里是否可以进行订购，或者我应该在我的JAVA应用程序中处理？

- Alexander Farber

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marth · Accepted Answer

在array_agg中指定ORDER BY：

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;

返回值（使用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"
    }
  ]
}