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
Last activity: Jan 18, 2025, 02:07 PM