Conflict between JSON_AGG and COPY

[Đỗ Ngọc Trí Cường <dntcuong@xxxxxxxxxxxx>]

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';

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.

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@xxxxxxxxxxxx
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.