DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on Edited on

PostgreSQL 19: Exporting Data in JSON Format Using COPY TO

PostgreSQL provides the COPY command to efficiently transfer data between database tables and files or standard input/output streams. Traditionally, COPY supports formats such as TEXT, CSV, and BINARY.
However, exporting relational data directly as JSON has often required additional SQL functions, such as row_to_json() or json_agg().
PostgreSQL 19 introduces a new capability: native JSON format support for COPY TO.
With this enhancement, we can export table data or query results directly in JSON format without explicitly converting every row using JSON functions.
In this article, I will demonstrate this feature using PostgreSQL 19 and compare the traditional approach with the new syntax.

Preparing the Test Environment

First, let's create a sample table named tbl_person.

vahiddb=# CREATE TABLE tbl_person (
    id          INTEGER,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    department  VARCHAR(50),
    job_title   VARCHAR(100),
    salary      NUMERIC(10,2),
    active      BOOLEAN,
    hire_date   DATE,
    profile     JSONB
);
CREATE TABLE
Enter fullscreen mode Exit fullscreen mode

Next, insert five sample records:

vahiddb=# INSERT INTO tbl_person  (
    id, first_name, last_name, department,
    job_title, salary, active, hire_date, profile
)
VALUES
(1, 'Vahid', 'Yousefzadeh', 'DBA', 'Senior Oracle DBA',
 8500, TRUE, '2011-09-01',
 '{"city":"Babol","skills":["Oracle","PostgreSQL"]}'),

(2, 'Nader', 'DastNeshan', 'Sports', 'Football Coach',
 6500, TRUE, '2015-03-10',
 '{"city":"Mazandaran","skills":["Football Coach"]}'),

(3, 'Sadegh', 'Varmaziar', 'Sports', 'Football Coach',
 7200, TRUE, '2018-06-15',
 '{"city":"Sari","skills":["Football Coach"]}'),

(4, 'Masoud', 'Sori', 'DBA', 'Oracle DBA',
 5800, FALSE, '2020-01-20',
 '{"city":"Babol","skills":["SQL Server","Oracle"]}'),

(5, 'Payan', 'Rafat', 'Sports', 'Football Coach',
 9500, TRUE, '2010-11-05',
 '{"city":"Tehran","skills":["Football Coach"]}');
INSERT 0 5
Enter fullscreen mode Exit fullscreen mode

Let's verify the inserted records:

vahiddb=# SELECT * FROM tbl_person;
 id | first_name |  last_name  | department |     job_title     | salary  | active | hire_date  |                        profile
----+------------+-------------+------------+-------------------+---------+--------+------------+-------------------------------------------------------
  1 | Vahid      | Yousefzadeh | DBA        | Senior Oracle DBA | 8500.00 | t      | 2011-09-01 | {"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}
  2 | Nader      | DastNeshan  | Sports     | Football Coach    | 6500.00 | t      | 2015-03-10 | {"city": "Mazandaran", "skills": ["Football Coach"]}
  3 | Sadegh     | Varmaziar   | Sports     | Football Coach    | 7200.00 | t      | 2018-06-15 | {"city": "Sari", "skills": ["Football Coach"]}
  4 | Masoud     | Sori        | DBA        | Oracle DBA        | 5800.00 | f      | 2020-01-20 | {"city": "Babol", "skills": ["SQL Server", "Oracle"]}
  5 | Payan      | Rafat       | Sports     | Football Coach    | 9500.00 | t      | 2010-11-05 | {"city": "Tehran", "skills": ["Football Coach"]}
(5 rows)
Enter fullscreen mode Exit fullscreen mode

Traditional Approach: Using row_to_json()

Before PostgreSQL 19, one way to export query results as JSON objects was to use the row_to_json() function.
For example:

vahiddb=# COPY (
    SELECT row_to_json(tbl_person) FROM tbl_person
) TO STDOUT;

{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
Enter fullscreen mode Exit fullscreen mode

We can also save the output directly to a file on the database server:

vahiddb=# COPY (
    SELECT row_to_json(tbl_person) FROM tbl_person
) TO '/backup/tbl_person.json';
COPY 5

[postgres@OEL98-PG ~]$ cat /backup/tbl_person.json
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
Enter fullscreen mode Exit fullscreen mode

As shown above, the traditional approach works, but it requires explicitly calling row_to_json().

PostgreSQL 19: Native JSON Format in COPY TO

PostgreSQL 19 simplifies this operation by introducing the FORMAT JSON option. Instead of using row_to_json(), we can directly execute:

vahiddb=# copy tbl_person to '/backup/tbl_person.json' with (format json);
COPY 5
Enter fullscreen mode Exit fullscreen mode
[postgres@OEL98-PG ~]$ cat /backup/tbl_person.json
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
Enter fullscreen mode Exit fullscreen mode

The output contains the same JSON objects as the previous example.
The important difference is the SQL syntax. We no longer need to explicitly convert each row using row_to_json().
We can also send the output directly to the client using STDOUT:

vahiddb=# COPY tbl_person TO STDOUT WITH (FORMAT JSON);
{"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
Enter fullscreen mode Exit fullscreen mode

Notice that PostgreSQL produces one JSON object per row. This format is known as Newline-Delimited JSON (NDJSON).

Using FORCE_ARRAY to Generate a JSON Array

What if an application expects a single JSON document containing an array of objects instead of separate JSON objects on individual lines?
PostgreSQL 19 introduces another option called FORCE_ARRAY.
Let's execute:

vahiddb=# COPY tbl_person TO STDOUT WITH (FORMAT JSON, FORCE_ARRAY);
[
 {"id":1,"first_name":"Vahid","last_name":"Yousefzadeh","department":"DBA","job_title":"Senior Oracle DBA","salary":8500.00,"active":true,"hire_date":"2011-09-01","profile":{"city": "Babol", "skills": ["Oracle", "PostgreSQL"]}}
,{"id":2,"first_name":"Nader","last_name":"DastNeshan","department":"Sports","job_title":"Football Coach","salary":6500.00,"active":true,"hire_date":"2015-03-10","profile":{"city": "Mazandaran", "skills": ["Football Coach"]}}
,{"id":3,"first_name":"Sadegh","last_name":"Varmaziar","department":"Sports","job_title":"Football Coach","salary":7200.00,"active":true,"hire_date":"2018-06-15","profile":{"city": "Sari", "skills": ["Football Coach"]}}
,{"id":4,"first_name":"Masoud","last_name":"Sori","department":"DBA","job_title":"Oracle DBA","salary":5800.00,"active":false,"hire_date":"2020-01-20","profile":{"city": "Babol", "skills": ["SQL Server", "Oracle"]}}
,{"id":5,"first_name":"Payan","last_name":"Rafat","department":"Sports","job_title":"Football Coach","salary":9500.00,"active":true,"hire_date":"2010-11-05","profile":{"city": "Tehran", "skills": ["Football Coach"]}}
]
Enter fullscreen mode Exit fullscreen mode

The FORCE_ARRAY option changes the output structure from separate JSON objects into a single JSON array.
This is useful when integrating PostgreSQL with applications and services that expect a complete JSON array.

Exporting Selected Columns

Another useful capability is exporting only the columns required by the consuming application.
For example:

vahiddb=# COPY (SELECT first_name, last_name FROM tbl_person ) TO STDOUT WITH (FORMAT JSON);
{"first_name":"Vahid","last_name":"Yousefzadeh"}
{"first_name":"Nader","last_name":"DastNeshan"}
{"first_name":"Sadegh","last_name":"Varmaziar"}
{"first_name":"Masoud","last_name":"Sori"}
{"first_name":"Payan","last_name":"Rafat"}
Enter fullscreen mode Exit fullscreen mode

As demonstrated, FORMAT JSON also works with query results, not just direct table exports.
This provides flexibility when exporting specific columns, filtering records, joining tables, or generating calculated values.

Top comments (0)