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" <> 
To: "Đỗ Ngọc Trí Cường" <>, "pgsql-general" 
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); 

(1, 'john', 'John'), 
(2, 'anna', 'Anna'), 
(3, 'sussi', 'Sussi'), 
(4, 'david', 'David Beckham'), 
(5, 'wayne', 'Wayne Rooney'); 

This can be shortened to: 

(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. 

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; 
[{"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; 
{"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; 

(1 row) 

which then gets turned back into JSON: 

select ARRAY_TO_JSON(array_agg(row_to_json(t))) from test_table t; 

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> 


DIGI-TEXX | a global BPO provider 

Address: Anna Building, Quang Trung Software City, 

District. 12, Ho Chi Minh City, Vietnam 

Website: <> 



/*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 

/*Please consider the environment before printing./ 


Adrian Klaver 

Reply via email to