Dear Arian Klaver,
I think there is a misunderstood here.
I think that I quite understand how is the second query run.
The question I asked here is why exporting data, which is generated by a query
"JSON_AGG" , with command "COPY". The data contain "\n" as 2 characters instead
of "new line" character.
The second query is for the old version of PostgreSQL (9.3 and previous) cause
of they don't have JSON_AGG aggregate function. Since 9.4, we have "JSON_AGG"
already. So I want to rewrite and reduce the length of the query. But it is
don't work as I expected with command COPY.
Thank you and best regards,
Đỗ Ngọc Trí Cường (Mr.) | Software Development Department | +84 28 3715 5325
From: "Adrian Klaver" <adrian.kla...@aklaver.com>
To: "Đỗ Ngọc Trí Cường" <dntcu...@digi-texx.vn>, "pgsql-general"
<pgsql-general@lists.postgresql.org>
Sent: Monday, April 9, 2018 12:59:44 AM
Subject: Re: Conflict between JSON_AGG and COPY
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';
BQ_BEGIN
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.
BQ_END
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"
