Exporting JSON Lines From a Query in PostgreSQL Safely

JSON Lines (also called newline-delimited JSON or NDJSON) is a simple format: one complete JSON value per line, no outer array, no commas between records. It is the format that log systems, bulk APIs, DuckDB and machine learning pipelines prefer, because a file can be appended to, split by lines, processed as a stream and read with constant memory. PostgreSQL produces JSON natively, so exporting a query as JSON Lines takes one statement. The detail that bites is escaping when you use COPY. This article shows the clean ways, the type behavior and the choices for large exports.

One object per row

to_jsonb converts a row to a JSON object keyed by column name:

SELECT to_jsonb(c) FROM customer c LIMIT 2;
-- {"id": 1, "email": "[email protected]", "created_at": "2026-03-05T10:00:00+00:00", "balance": 120.50}

row_to_json and to_json give json instead of jsonb; the differences matter for output. jsonb normalizes: it sorts keys by length then alphabetically, removes duplicate keys and uses a single space after colons and commas. json preserves the text as built. For an export, either works, and jsonb output is stable and compact enough; if you want key order that matches the column order, use row_to_json. To rename keys, reshape or leave out columns, build the object explicitly:

SELECT json_build_object('id', id, 'email', lower(email), 'signup', created_at::date) FROM customer;

Nested data comes from subqueries and aggregates: json_build_object('id', o.id, 'lines', (SELECT json_agg(l) FROM order_line l WHERE l.order_id = o.id)) embeds each order's lines inside the order object, producing a document per order without a client-side join. Version 16 and later also support JSON_OBJECT and JSON_ARRAYAGG with SQL standard syntax, as described in SQL JSON support added in recent versions.

Write it out

From psql, the straightforward way is to print only the tuples with no alignment or headers and redirect:

psql -d shop -At -c "SELECT to_jsonb(c) FROM customer c" > customers.jsonl

-A turns off alignment, -t suppresses headers and footers. Each value prints on its own line. This works well for moderate sizes, and it passes through psql's output machinery. For large results or exports from an application, use COPY:

COPY (SELECT to_jsonb(c) FROM customer c) TO STDOUT;

and here the escaping trap appears. COPY text format treats backslash as an escape character and doubles it on output, so a JSON string containing \" or \n comes out as \\" and \\n, which is no longer valid JSON. A JSON value with newlines inside strings is escaped as \n in JSON already, but COPY then prints \\n. Two ways around it. Use CSV format with the quote and escape set to a character that does not occur, which turns off special treatment: COPY (...) TO STDOUT WITH (FORMAT csv, QUOTE E'\\x01', DELIMITER E'\\x02') is a known idiom that writes the line unchanged because the delimiter and quote do not appear in the data. Or avoid COPY and fetch the rows through a driver, writing each value as a line yourself, which is clean and more code. Test the output with jq -c . customers.jsonl > /dev/null, which fails on the first invalid line.

Types and nulls

JSON has fewer types than PostgreSQL, and conversions follow rules. integer, bigint and numeric become JSON numbers; very large bigint and numeric values lose precision in consumers that read numbers as doubles (JavaScript above 2^53), so cast to text when exact digits matter: id::text AS id. boolean becomes true or false. timestamptz becomes an ISO 8601 string in the session time zone, with an offset, so set TimeZone to UTC for the export to get stable output. date becomes "2026-03-05". interval becomes a string like "1 day 02:00:00". bytea becomes a hex string with \x prefix, escaped. uuid and text become strings. Arrays become JSON arrays, composite types become objects, and NULL becomes null. NaN and infinity in numeric columns have no JSON representation, and PostgreSQL emits them as strings or errors depending on the type, so clean them first. To omit null keys, use jsonb_strip_nulls(to_jsonb(c)).

Decide the contract with the consumer: key names, date format, how money is represented and how missing values appear. Document it, since "same query, different session time zone" produces different files.

Large exports

A million rows is not a problem; a billion is. Stream, and do not accumulate. COPY ... TO STDOUT streams from the server and a driver's copy API (copy_expert in psycopg2, cursor.copy in psycopg 3, CopyManager.copyOut in JDBC) writes to a file or pipe as data arrives. A server-side cursor does the same row by row, as explained in streaming query results without loading everything. Compress on the fly: psql ... | gzip > customers.jsonl.gz or zstd, since JSON compresses by ten times or more. Split the output by key ranges or dates into several files for parallel processing and easy retries, naming them predictably. Run against a replica to keep the load off the primary, and in a repeatable-read transaction if the export needs a consistent snapshot.

Verify

Count lines and compare with SELECT count(*) for the same query: wc -l customers.jsonl. Check that every line parses: jq -c . file > /dev/null. Spot check a few records against the table. Check that the file ends with a newline, which some consumers require. If the file feeds DuckDB, a quick read tells you whether types were inferred as expected: SELECT * FROM read_json_auto('customers.jsonl') LIMIT 5. Record the query, the time and the row count with the file, so the export is reproducible and auditable.

Reading it back

The same format loads well. A staging table with one jsonb column and COPY ... FROM STDIN ingests JSON Lines, subject to the same escaping care, and jsonb_populate_record or JSON_TABLE shreds each document into columns, as in using COPY with STDIN from an application. For changes over time, writing the output of a query with an updated_at > $last filter produces incremental files that the other side appends or merges.

When CSV or Parquet is the better format

Choose CSV when the consumer is a spreadsheet or a tool that expects rows and columns, and the data is flat. It is smaller and simpler. Choose Parquet when the file will be queried repeatedly by an analytics engine, since columnar compression and typed columns make it far faster to scan, as covered in exporting data to Parquet from PostgreSQL. Choose JSON Lines when the records are nested or have optional fields, when the consumer is an API or a log pipeline, or when schema flexibility matters more than size. Often a pipeline uses JSON Lines at the edges and Parquet inside.

What to do next

Export a thousand rows of one table to JSON Lines with psql -At, check them with jq, and then try the COPY form to see the backslash behavior yourself. Set the session time zone to UTC and cast big integers to text. The manual documents the functions in JSON functions and operators and the escaping rules in COPY. The hub is reading and writing files from PostgreSQL.