Export PostgreSQL to JSON

Export PostgreSQL data as JSON.

PostgreSQL can build JSON in SQL, which makes exporting it mostly a question of shape: one array containing everything, or one object per line. The second scales and the first does not.

Short answer: Use json_agg with psql's -t -A flags for a single JSON array — simple, and fine up to a few hundred thousand rows. For anything larger use JSON Lines: one row_to_json object per line, which streams instead of building the whole document in memory. Avoid plain COPY TO for JSON, because its text format escapes backslashes and corrupts the output.

Download PgDeck View pricing

PgDeck PostgreSQL desktop client with schema navigation, table filtering, row editing, and SQL tools

Why developers use PgDeck

PgDeck is shaped around PostgreSQL tasks that happen every day: connect, inspect, filter, edit, query, manage structure, copy, and export.

psql -t -A is required or the output is padded and unparseable

json_agg builds the whole document in memory; JSON Lines streams

json_build_object gives you full control over keys and nesting

COPY's text format escapes backslashes and breaks JSON

jsonb reorders keys and drops duplicates; json preserves them

Secure connections

Save PostgreSQL connection details with encrypted passwords, SSL settings, URL import, testing, search, edit, and delete actions.

Schema and data grid

Browse schemas and tables, search the table list, pin favorites, filter rows, sort columns, resize columns, and paginate results.

SQL workspace

Run SELECT and non-SELECT statements from multi-tab SQL editors with syntax highlighting and interactive query results.

Staged editing

Edit cells, add rows, duplicate rows, set NULL, stage deletes, review pending changes, and commit everything in one transaction.

How to export PostgreSQL data to JSON

1
A single JSON array
json_agg collects rows into one array. The psql flags matter: -t drops the column header and row-count footer, and -A turns off the column alignment padding that would otherwise corrupt the JSON.
```
psql -t -A -d mydb -c "SELECT json_agg(t) FROM (SELECT id, email, created_at FROM users WHERE active) t" > users.json
```
2
Choose exactly which keys appear
row_to_json uses your column names as keys. When you want different names, a nested shape, or only some fields, build the object explicitly with json_build_object — it is clearer than aliasing columns and it lets you nest.
```
SELECT json_agg(json_build_object(
  'id', u.id,
  'email', u.email,
  'signedUpAt', u.created_at,
  'orderCount', (SELECT count(*) FROM orders o WHERE o.user_id = u.id)
))
FROM users u;
```
3
Use JSON Lines for anything large
json_agg builds the entire document in memory on the server before returning it, so a large table can exhaust memory or simply take a long time. JSON Lines — one object per line, no wrapping array — streams row by row and is what most data tools expect for bulk import anyway.
```
psql -t -A -d mydb -c "SELECT row_to_json(t) FROM (SELECT * FROM events) t" > events.jsonl
```
4
Do not use plain COPY TO for JSON
COPY's default text format escapes backslashes, so any \n or \" inside your JSON strings comes out doubled and the file no longer parses. If you must use COPY, the CSV format with a quote character that cannot appear in JSON avoids the mangling.
```
-- Corrupts embedded backslashes:
COPY (SELECT row_to_json(t) FROM users t) TO STDOUT;

-- Safe:
COPY (SELECT row_to_json(t) FROM users t) TO STDOUT
  WITH (FORMAT csv, QUOTE E'\x01', DELIMITER E'\x02');
```
Or export from a GUI client
For a one-off, filtering a table in a client and exporting the result as JSON avoids all of the above. PgDeck exports the current filtered view or a query result to JSON, CSV, or SQL INSERT statements, so what you export matches what you were looking at.

json and jsonb behave differently on export

json stores the text as written, preserving key order, whitespace, and duplicate keys. jsonb parses into a binary form, which reorders keys, removes duplicates, and normalises whitespace. For exports intended to be diffed or compared byte-for-byte, that difference matters — jsonb_agg will not give you back the document you put in.

Timestamps are the usual compatibility problem

row_to_json renders a timestamptz in PostgreSQL's output format, which depends on the session TimeZone setting and is not ISO 8601 with a T separator. Consumers expecting strict ISO input will reject it. Format it explicitly with to_char, or cast in the query, rather than fixing it downstream.

Why JSON Lines is usually the better default

A single array must be complete before anything can read it, so the producer holds it all in memory and the consumer parses it all at once. JSON Lines is one self-contained object per line: it streams, it can be split across files or workers, a partial file is still valid up to the last complete line, and it appends. Most bulk-import tools prefer it.

Nesting is where SQL-built JSON earns its keep

Exporting orders with their line items as a nested array is one query with a correlated json_agg subquery, rather than two exports and a join in application code afterwards. Building the shape in SQL keeps the export reproducible and moves the work to the database, which is generally where it belongs.

Watch what you are serialising

SELECT * into row_to_json exports every column including password hashes, session tokens, and internal flags, and JSON makes the field names self-documenting for whoever ends up with the file. Name your columns, or build the object explicitly. It costs a few seconds and it is the difference between an export and a leak.

How do I export a PostgreSQL query to JSON?

Wrap the query in json_agg and run it through psql with -t and -A so the output is not padded or decorated: psql -t -A -c "SELECT json_agg(t) FROM (SELECT ...) t" > out.json. For large result sets, use row_to_json per row to produce JSON Lines instead.

Why does my exported JSON not parse?

Three usual causes. Missing -t so psql included a header and row count. Missing -A so columns are padded with spaces. Or you used COPY TO in its default text format, which escapes backslashes and breaks any embedded \n or \" inside strings.

What is the difference between row_to_json and json_agg?

row_to_json converts one row into one JSON object, so a query returns many objects. json_agg aggregates all of those into a single JSON array. Use row_to_json for JSON Lines output and json_agg when you want one array document.

How do I export a large table to JSON without running out of memory?

Use JSON Lines rather than json_agg. json_agg assembles the entire array on the server before returning anything; row_to_json emits one object per row and streams. JSON Lines files also split cleanly and remain valid up to the last complete line.

Should I use json or jsonb for exports?

json preserves key order, whitespace, and duplicate keys exactly as written. jsonb normalises all three. If the export will be compared or diffed, use json; if you only need valid data, either works.