Selecting a JSON object of arrays from a PostgreSQL table

Started by Alexander Farberover 8 years ago4 messagesgeneral
Jump to latest
#1Alexander Farber
alexander.farber@gmail.com

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

#2Ivan E. Panchenko
i.panchenko@postgrespro.ru
In reply to: Alexander Farber (#1)
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 пишет:

Show quoted text

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&gt;

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

#3Alexander Farber
alexander.farber@gmail.com
In reply to: Ivan E. Panchenko (#2)
Re: Selecting a JSON object of arrays from a PostgreSQL table

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!

#4Alexander Farber
alexander.farber@gmail.com
In reply to: Alexander Farber (#3)
Re: Selecting a JSON object of arrays from a PostgreSQL table

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;