Re: Conflict between JSON_AGG and COPY

On 04/08/2018 08:44 PM, Đỗ Ngọc Trí Cường wrote:
> 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.

COPY
(select json_build_object('RECORDS', ARRAY_AGG(t) ) from test_table t) 
TO '/home/postgres/test2.json';

cat test2.json

{"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"}]}


> 
> Thank you and best regards,
> 
> 
> Đỗ Ngọc Trí*Cường*(Mr.) | *Software Development Department*| +84 28 3715 
> 5325


-- 
Adrian Klaver
adrian.klaver@aklaver.com