Re: Selecting a JSON object of arrays from a PostgreSQL table

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