Hi Alex!
Why not:
select json_object_agg(gid, y) from
(
select gid, jsonb_agg(row_to_json(chat)) y
from chat
group by gid
) x;
Regards,
Ivan
01.01.2018 16:41, Alexander Farber пишет:
> Hello and happy new year!
>
> I have prepared a simple SQL Fiddle demonstrating my problem:
> http://sqlfiddle.com/#!17/2c9c5/1 <http://sqlfiddle.com/#%2117/2c9c5/1>
>
> In a two-player game I store user chats in a table:
>
> CREATE TABLE chat(
> gid integer, /* game id */
> uid integer, /* user id */
> created timestamptz,
> msg text
> );
>
> Here I fill the table with a simple 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');
>
> And can fetch the data by running the SELECT query:
>
> SELECT ARRAY_TO_JSON(
> COALESCE(ARRAY_AGG(ROW_TO_JSON(x)),
> array[]::json[])) FROM (
> SELECT
> gid,
> uid,
> EXTRACT(EPOCH FROM created)::int AS created,
> msg
> FROM chat) x;
>
> which returns me a JSON-array:
>
> [{"gid":10,"uid":1,"created":1514813043,"msg":"msg 1"},
> {"gid":10,"uid":2,"created":1514813103,"msg":"msg 2"},
> {"gid":10,"uid":1,"created":1514813163,"msg":"msg 3"},
> {"gid":10,"uid":2,"created":1514813223,"msg":"msg 4"},
> {"gid":10,"uid":1,"created":1514813283,"msg":"msg 5"},
> {"gid":10,"uid":2,"created":1514813343,"msg":"msg 6"},
> {"gid":20,"uid":3,"created":1514813403,"msg":"msg 7"},
> {"gid":20,"uid":4,"created":1514813463,"msg":"msg 8"},
> {"gid":20,"uid":4,"created":1514813523,"msg":"msg 9"}]
>
> However I would like to use "gid" as JSON object properties and the
> rest data as values in that object:
>
> {"10": [{"uid":1,"created":1514813043,"msg":"msg 1"},
> {"uid":2,"created":1514813103,"msg":"msg 2"},
> {"uid":1,"created":1514813163,"msg":"msg 3"},
> {"uid":2,"created":1514813223,"msg":"msg 4"},
> {"uid":1,"created":1514813283,"msg":"msg 5"},
> {"uid":2,"created":1514813343,"msg":"msg 6"}],
>
> "20": [{"uid":3,"created":1514813403,"msg":"msg 7"},
> {"uid":4,"created":1514813463,"msg":"msg 8"},
> {"uid":4,"created":1514813523,"msg":"msg 9"}]}
>
> Is that please doable by using the PostgreSQL JSON functions?
>
> Thank you
> Alex
