From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Đỗ Ngọc Trí Cường <dntcuong(at)digi-texx(dot)vn>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Conflict between JSON_AGG and COPY |
Date: | 2018-04-08 17:59:44 |
Message-ID: | fe1c7756-e724-63a7-b200-b2361689b924@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 04/07/2018 03:44 AM, Đỗ Ngọc Trí Cường wrote:
> Dear all,
>
> I've found one case. I don't know this is a bug or I config/query some
> things wrong.
>
> Let I describe it. I have a table with structure and data is:
>
> id | username | fullname
> ----+-------------+---------------
> 1 | john | John
> 2 | anna | Anna
> 3 | sussi | Sussi
> 4 | david | David Beckham
> 5 | wayne | Wayne Rooney
>
> I want to export it to a file in JSON format so I run the query as below:
>
> COPY (SELECT row_to_json(t) FROM (SELECT json_agg(t1) AS "RECORDS"
> FROM test_table t1) t) TO '/home/postgres/test1.json';
>
> But the result I got will include "\n" in the result:
>
> {"RECORDS":[{"id":1,"username":"john","fullname":"John"}, \n
> {"id":2,"username":"anna","fullname":"Anna"}, \n
> {"id":3,"username":"sussi","fullname":"Sussi"}, \n
> {"id":4,"username":"david","fullname":"David Beckham"}, \n
> {"id":5,"username":"wayne","fullname":"Wayne Rooney"}]}
>
> Then, I try to get the same data in the other way:
>
> COPY (WITH t2 AS (select row_to_json(t) as js from test_table t),
> t1 AS (SELECT ARRAY_TO_JSON(array_agg(js)) as "RECORDS"
> FROM t2)
> SELECT row_to_json(t1) FROM t1)
> TO '/home/postgres/test2.json';
CREATE TABLE test_table (id integer, username varchar, fullname varchar);
INSERT INTO
test_table
VALUES
(1, 'john', 'John'),
(2, 'anna', 'Anna'),
(3, 'sussi', 'Sussi'),
(4, 'david', 'David Beckham'),
(5, 'wayne', 'Wayne Rooney');
This can be shortened to:
COPY
(select array_to_json(array_agg(row_to_json(t))) FROM test_table AS t)
TO '/home/postgres/test2.json';
>
> And the result I got is quite match what I expect.
>
> {"RECORDS":[{"id":1,"username":"john","fullname":"John"},{"id":2,"username":"anna","fullname":"Anna"},{"id":3,"username":"sussi","fullname":"Sussi"},{"id":4,"username":"david","fullname":"David
> Beckham"},{"id":5,"username":"wayne","fullname":"Wayne Rooney"}]}
>
>
> I think the COPY command does not the `\n` character for pretty in
> `json_agg` command.
Well in the first case you are starting by concatenating the 5 rows in
the table into a single row with the table rows separated by new lines:
SELECT json_agg(t1) AS "RECORDS" FROM test_table t1;
RECORDS
-----------------------------------------------------------
[{"id":1,"username":"john","fullname":"John"}, +
{"id":2,"username":"anna","fullname":"Anna"}, +
{"id":3,"username":"sussi","fullname":"Sussi"}, +
{"id":4,"username":"david","fullname":"David Beckham"}, +
{"id":5,"username":"wayne","fullname":"Wayne Rooney"}]
(1 row)
In the second case you start by maintaining the separate table rows:
select row_to_json(t) as js from test_table t;
js
--------------------------------------------------------
{"id":1,"username":"john","fullname":"John"}
{"id":2,"username":"anna","fullname":"Anna"}
{"id":3,"username":"sussi","fullname":"Sussi"}
{"id":4,"username":"david","fullname":"David Beckham"}
{"id":5,"username":"wayne","fullname":"Wayne Rooney"}
(5 rows)
and then keeping that as an array of arrays:
select array_agg(row_to_json(t)) from test_table t;
{"{\"id\":1,\"username\":\"john\",\"fullname\":\"John\"}","{\"id\":2,\"username\":\"anna\",\"fullname\":\"Anna\"}","{\"id\":3,\"username\":\"sussi\",\"fullname\":\"Sussi\"}","{\"id\":4,\"username\":\"david\",\"fullname\":\"David
Beckham\"}","{\"id\":5,\"username\":\"wayne\",\"fullname\":\"Wayne
Rooney\"}"}
(1 row)
which then gets turned back into JSON:
select ARRAY_TO_JSON(array_agg(row_to_json(t))) from test_table t;
[{"id":1,"username":"john","fullname":"John"},{"id":2,"username":"anna","fullname":"Anna"},{"id":3,"username":"sussi","fullname":"Sussi"},{"id":4,"username":"david","fullname":"David
Beckham"},{"id":5,"username":"wayne","fullname":"Wayne Rooney"}]
>
> Please help me give me your idea. Am I wrong or this is really a bug?
>
> Thank you and best regards,
>
> Đỗ Ngọc Trí*Cường*(Mr.)
>
> *Software Development Dept.*
>
>
>
> Mobile: +84 9 3800 3394 <tel:+84917220706>
>
> Phone: +84 28 3715 6322 <callto:+84%208%203715%205325>
>
> Email: dntcuong(at)digi-texx(dot)vn
>
> DIGI-TEXX | a global BPO provider
>
> Address: Anna Building, Quang Trung Software City,
>
> District. 12, Ho Chi Minh City, Vietnam
>
> Website: www.digi-texx.vn <http://www.digi-texx.vn/>
>
> //
>
> /IMPORTANT NOTICE:/
>
> /*This e-mail and any attachments may contain confidential and/or
> privileged information. If you are not the intended recipient, please
> delete it and notify the sender immediately. Any unauthorized copying,
> disclosure or distribution of the material in this e-mail is strictly
> forbidden./
>
> /*Please consider the environment before printing./
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Rory Campbell-Lange | 2018-04-08 18:01:55 | Re: dump/restore problem due to CVE-2018-1058 (9.5.12) |
Previous Message | Jeff Janes | 2018-04-08 15:53:31 | Re: Sum of written buffers bigger than allocation? |