Postgres Pro
Downloads
Home   >   mailing lists

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

Selecting a JSON object of arrays from a PostgreSQL table

From
Alexander Farber
Date:
Hello and happy new year!

I have prepared a simple SQL Fiddle demonstrating my problem: http://sqlfiddle.com/#!17/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

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

From
"Ivan E. Panchenko"
Date:
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

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

From
Alexander Farber
Date:
Hi Ivan,

On Mon, Jan 1, 2018 at 3:34 PM, Ivan E. Panchenko <i.panchenko@postgrespro.ru> wrote:

select json_object_agg(gid, y) from
    (
         select gid, jsonb_agg(row_to_json(chat)) y
         from chat
         group by gid
    ) x;


01.01.2018 16:41, Alexander Farber пишет:

 I have prepared a simple SQL Fiddle demonstrating my problem: http://sqlfiddle.com/#!17/2c9c5/1 


that is great, thank you!

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

From
Alexander Farber
Date:
Also got this nice suggestion at https://stackoverflow.com/q/48050127/165071 -

SELECT COALESCE(
  json_object_agg(
          gid, array_to_json(y)
        ), '{}'::json)
FROM    (
  SELECT  gid,
          array_agg(
            json_build_object(
              'uid', uid,
              'created', EXTRACT(EPOCH FROM created)::int,
              'msg', msg)
          ) AS y
  FROM    chat /* WHERE gid = 0 */
  GROUP BY gid
) x;