From: | Đỗ Ngọc Trí Cường <dntcuong(at)digi-texx(dot)vn> |
---|---|
To: | pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Conflict between JSON_AGG and COPY |
Date: | 2018-04-07 10:44:13 |
Message-ID: | 152647427.13167958.1523097853639.JavaMail.zimbra@digi-texx.vn |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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:
BQ_BEGIN
{"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"}]}
BQ_END
Then, I try to get the same data in the other way:
BQ_BEGIN
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';
BQ_END
And the result I got is quite match what I expect.
BQ_BEGIN
{"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"}]}
BQ_END
I think the COPY command does not the `\n` character for pretty in `json_agg` command.
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
Phone: +84 28 3715 6322
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
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Rory Campbell-Lange | 2018-04-07 19:27:57 | dump/restore problem due to CVE-2018-1058 (9.5.12) |
Previous Message | Thomas Poty | 2018-04-07 07:57:49 | Re: dealing with lock |