Re: Conflict between JSON_AGG and COPY - Mailing list 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@digi-texx.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.klaver@aklaver.com