1

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
5
  • Why use COPY (select ...) when you don't need or want the COPY format? Why not do the SELECT query directly without any unescaping of the results? Oct 31, 2022 at 12:12
  • @DanielVérité want the text format, not the psql output with the ascii markup etc. This is standard method to get the 'clean' output. Oct 31, 2022 at 12:19
  • 1
    The psql decoration is turned off with psql -A -t which are shortcuts to set the unaligned format (no ascii markers and padding around fields) and the "tuples_only" mode (no header/footer) Oct 31, 2022 at 14:03
  • @DanielVérité thanks! That seemed to do the trick :) Can I assume it uses the default platform encoding for the output? Nov 1, 2022 at 12:33
  • 1
    Yes, it's the client_encoding setting. It's determined from the client-side LC_CTYPE or can be set explicitly at connection time or later with SET client_encoding=.... Nov 2, 2022 at 10:37

2 Answers 2

0

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.

2
  • Ah. I expected there would be only 1 (giant) row, actually. Oct 31, 2022 at 10:52
  • Also BINARY generates a custom binary format, not usable JSON Oct 31, 2022 at 10:59
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.