Often in PostgreSQL you may want to export a table to a JSON file.
You can use the COPY statement with the following syntax to do so:
COPY (
SELECT json_agg(row_to_json(athletes))::text
FROM athletes
) TO 'c/users/bob/downloads/athletes.json';
This particular example will export all rows from the athletes table to a JSON file named athletes.json in the location on our computer that we specified.
The following example shows how to use this syntax in practice.
Example: How to Export Table to JSON File in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER,
team TEXT,
points INTEGER
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 22);
INSERT INTO athletes VALUES (0002, 'Mavs', 30);
INSERT INTO athletes VALUES (0003, 'Mavs', 12);
INSERT INTO athletes VALUES (0004, 'Mavs', 9);
INSERT INTO athletes VALUES (0005, 'Mavs', 14);
INSERT INTO athletes VALUES (0006, 'Heat', 16);
INSERT INTO athletes VALUES (0007, 'Heat', 40);
INSERT INTO athletes VALUES (0008, 'Heat', 18);
INSERT INTO athletes VALUES (0009, 'Heat', 16);
INSERT INTO athletes VALUES (0010, 'Heat', 33);
-- view all rows in table
SELECT * FROM athletes;
id | team | points
----+------+--------
1 | Mavs | 22
2 | Mavs | 30
3 | Mavs | 12
4 | Mavs | 9
5 | Mavs | 14
6 | Heat | 16
7 | Heat | 40
8 | Heat | 18
9 | Heat | 16
10 | Heat | 33
Suppose that we would like to export this table to a JSON file named athletes.json.
We can use the following syntax to do so:
COPY (
SELECT json_agg(row_to_json(athletes))::text
FROM athletes
) TO 'c/users/bob/downloads/athletes.json';
Once we have exported this table, we can then navigate to the location on our computer where we exported it and view it:
[{"id":1,"team":"Mavs","points":22},
{"id":2,"team":"Mavs","points":30},
{"id":3,"team":"Mavs","points":12},
{"id":4,"team":"Mavs","points":9},
{"id":5,"team":"Mavs","points":14},
{"id":6,"team":"Heat","points":16},
{"id":7,"team":"Heat","points":40},
{"id":8,"team":"Heat","points":18},
{"id":9,"team":"Heat","points":16},
{"id":10,"team":"Heat","points":33}]
We can see that the athletes.json file contains the data from our athletes table in a JSON format.
Feel free to use similar syntax to export your own table from PostgreSQL to a JSON file.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Insert If Not Exists Already
PostgreSQL: How to Insert Values into Table from Select Query
PostgreSQL: How to Update Multiple Columns in Table
