Unexpected newlines in json output in Postgresql

Question

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

Answer 1

The TEXT format is specified in the documentation to have each row on a new line. If there are embedded newline characters in the value then these will be encoded with \n.

Text Format

When the text format is used, the data read or written is a text file with one line per table row. Columns in a row are separated by the delimiter character. The column values themselves are strings generated by the output function, or acceptable to the input function, of each attribute's data type.
..snip..
The following special backslash sequences are recognized

Sequence	Represents
\n	Newline (ASCII 10)

..snip..
Presently, COPY TO will never emit an octal or hex-digits backslash sequence, but it does use the other sequences listed above for those control characters.

Instead you probably want the BINARY format option.

Answer 2

I found at least a workaround, found here:

https://alphahydrae.com/2021/02/how-to-export-postgresql-data-to-a-json-file/

Note that you can convert multiple rows into a JSON array with just json_agg, without using row_to_json, but for some reason converting that array to text will introduce line breaks into the resulting text, and the COPY command will serialize those line breaks into literal \n characters instead of actual line breaks.

Changing select json_agg(Y.*) to select json_agg(row_to_json(Y.*)) seems to do the trick.

Now only the output is double escaped, so I need a sed 's/\\\\/\\/g' before it is accepted in jq