Sample Header Ad - 728x90

Unexpected newlines in json output in Postgresql

2 votes
2 answers
2366 views
I'm squashing a datastructure to JSON objects using the PostgreSQL Json functions, however, there are unexpected newlines in the output. Basically, it creates JSON objects and put them in a list. This is the SQL: copy ( select json_agg(Y.*) from ( select table_logical_id as "logicalId", max(table_actual_name) as "tableName", max(table_display_name) as "displayName", max(table_restriction_expression) as "restrictionExpression", json_strip_nulls(json_agg( json_build_object('field' , column_actual_name, 'type' , case column_type when 'DATE_TIME' then 'DATE' else column_type end, 'displayName' , column_display_name, 'displayLocation' , column_display_location, ) ORDER BY column_order ASC )) as "columns", coalesce( json_agg( json_build_object( 'field' , column_actual_name, 'direction' , case column_is_asc_sort when true then 'ASC' else 'DESC' end ) order by column_sort_order desc ) filter (where column_sort_order is not null) , '[]') as "defaultSort" from my_table_config group by table_logical_id ) Y ) TO STDOUT WITH (FORMAT TEXT, ENCODING 'UTF8'); The output contains a literal \n (so 2 characters, not the line feed control character) between each json record, like this: ..."direction" : "ASC"}]}, \n {"logicalId ... Is this a bug in PostgreSQL? Or, how can I prevent this? UPDATE: Exporting a BINARY as suggested in the answer does not work, as that returns something that starts with: 00000000: 5047 434f 5059 0aff 0d0a 0000 0000 0000 PGCOPY.......... 00000010: 0000 0000 0100 7480 cc5b 7b22 6c6f 6769 ......t..[{"logi 00000020: 6361 6c49 6422 3a22 4155 4449 545f 3230 calId":"AUDIT_20
Asked by Rob Audenaerde (213 rep)
Oct 31, 2022, 09:46 AM
Last activity: Jan 18, 2025, 02:07 PM