Export Postgres table as json

Question

Is there a way to export postgres table data as json to a file? I need the output to be line by line, like:

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

EDIT: postgres version: 9.3.4

Answer 1

Try here for a basic intro to PostgreSQL and JSON.

Also, PostgreSQL documentation is pretty good, so try it here. Check out the pretty_bool option.

Your original question was "Is there a way to export postgres table data as JSON". You wanted it in this format

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

I didn't have a running instance of PostgreSQL so I downloaded, compiled and installed 9.4.

To answer this, I first CREATEed a table (fred)

CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20));
INSERT INTO fred VALUES (2, 43, 'asfasfasfd' );
INSERT INTO fred VALUES (3, 435, 'ererere' );
INSERT INTO fred VALUES (6, 43343, 'eresdfssfsfasfae');

Then, to check:

test=# select * from fred;
 mary | jimmy | paulie 
------+-------+------------------
 2 | 43 | asfasfasfd
 3 | 435 | ererere
 6 | 43343 | eresdfssfsfasfae

Then I issued this command

test=# COPY (SELECT ROW_TO_JSON(t) 
test(# FROM (SELECT * FROM fred) t) 
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';
COPY 3
test=# 

I then quit psql and listed the file myfile.

test=# \q
[pol@polhost inst]$ more myfile 
{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
{"mary":3,"jimmy":435,"paulie":"ererere"}
{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
[pol@polhost inst]$

(you can experiment with the output from

COPY (SELECT ROW_TO_JSON(t, TRUE) -- <-- Note addition of "TRUE" here!

at your leisure).

It was pointed out by @offby1 that the output (while corresponding to the OP's question) is not correct JSON. @EvanCarroll pointed out that \o is also a way of outputting to a file, so I combined the solutions to these two niggles in this statement (with help from here):

test=# \o out.json
test=# SELECT array_to_json(array_agg(fred), FALSE) AS ok_json FROM fred;
 -- <-- "TRUE" here will produce plus
 ("+) signs in the output. "FALSE"
 is the default anyway.
test=# \o

gives:

[pol@polhost inst]$ more out.json 
 ok_json 
----------------------------------------------------------------------------------------------------------------------------------------------
 [{"mary":2,"jimmy":43,"paulie":"asfasfasfd"},{"mary":3,"jimmy":435,"paulie":"ererere"},{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}]
(1 row)
[pol@polhost inst]$ 

FINALLY, there is the backslash (\) problem alluded to by @AdamGent in his post. This was a bit tricky, but it is possible without resorting to post-query processing. Voilà:

INSERT INTO fred VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred VALUES (3, 44545, '\sdfs\\\sfs\\gf');

And using REGEXP_REPLACE thus (note the cast ::TEXT) removes the excess blackslashes.

test=# \o slash.json
test=# SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g') 
test=# FROM (SELECT * FROM fred) AS t; -- I found that using a CTE was helpful for legibility
test=# \o
test=# \q

gives:

[pol@polhost inst]$ more slash.json 
 regexp_replace 
------------------------------------------------------
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
(5 rows)
[pol@polhost inst]$ 

(p.s. As for @Zoltán 's comment - this may be a version thing - unable to reproduce!).

• offby1: That seems to be exactly what the original poster wanted. Note, however, that while each row is proper JSON, the collection of rows isn't, since it lacks commas separating the rows, and square brackets surrounding them.
• Adam Gent: This will NOT work if you have any backslash in your columns!!!! Read carefully the COPY doc as it does special things for backslash characters (like adding another backslash).
• FacePalm: READ @AdamGent 's answer below to solve the backslash issue
• Peter Krauss: So... year 2017 and NO WAY TO EXPORT JSON with COPY command PostgreSQL?? There are CSV option, TXT option... Why not an JSON option?
• Peter Krauss: Thanks @Vérace. And sorry, now I tested a COPY with complex JSONb and the proceded JSON was fine, "proper JSON"!

Answer 2

If you're using psql then there is no reason to use \COPY at all.

\t
\a
\o file.json
SELECT row_to_json(r) FROM my_table AS r;

This is the same method we use to get png/jpgs/tifs out of the database with PostGIS for quick tests, and also to generate script files with PostgreSQL extensions.

• Peter Krauss: Great! As usual COPY command "not allow relative path", the psql-native-commands is the easiest way to copy into relative path! PS: there are a "terminal way" to use real COPY command with relative path, see here. psql -h remotehost -d remote_mydb -U myuser -c "COPY (SELECT '{\"x\":1,\"y\":[\"a\",2]}'::json AS r) TO STDOUT" > ./relative_path/file.csv
• Flimm: Note that the output in the file file.json is not JSON, rather, ever line in that file is a JSON object that represents a row. But the file altogether is not valid JSON, you would need to wrap the whole file in between [ and ] and add the needed commas at the end of lines.
• Mariano Ruiz: Yes as mentioned above, because each line is a row, the final output is also called JSON line format (where each line is a valid JSON object). Anyway, most tools deal automatically with this format like jq.
• Mariano Ruiz: Worth to mention that instead of doing first \t + Enter and then \a + Enter ... you can do all in one input: \t\a\o file.json + Enter

Answer 3

Please find below the only answer that outputs actually valid JSON (i.e. array of objects).

\t
\a
\o data.json
select json_agg(t) FROM (SELECT * from table) t;

(source)

• backus: Should be higher up. Thank you!
• sdoxsee: or directly from command line using flags here: stackoverflow.com/a/22467301/1098564, psql -d postgres -qAtX -c "select json_agg(t) FROM (SELECT * from table) t;" -o data.json

Answer 4

I will add a special caveat to Verace's answer. You need to do post processing on the outputted JSON file if you have text columns with backslash characters: \.

Otherwise you will get duplicate (\ -> \\) at best and completely invalid JSON at worse ie:

This:

{ "f1" : "crap\""}.

Becomes

{ "f1" : "crap\\""}.

Which looks fine but is completely invalid JSON.

You can replace the \\ into \ with sed:

sed -i -e 's/\\\\/\\/g' PG_OUT_JSON_FILE.json

From Postgres COPY where they round about mention it:

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. Any other backslashed character that is not mentioned in the above table will be taken to represent itself. However, beware of adding backslashes unnecessarily, since that might accidentally produce a string matching the end-of-data marker (.) or the null string (\N by default). These strings will be recognized before any other backslash processing is done.

It is strongly recommended that applications generating COPY data convert data newlines and carriage returns to the \n and \r sequences respectively. At present it is possible to represent a data carriage return by a backslash and carriage return, and to represent a data newline by a backslash and newline. However, these representations might not be accepted in future releases. They are also highly vulnerable to corruption if the COPY file is transferred across different machines (for example, from Unix to Windows or vice versa).

COPY TO will terminate each row with a Unix-style newline ("\n"). Servers running on Microsoft Windows instead output carriage return/newline ("\r\n"), but only for COPY to a server file; for consistency across platforms, COPY TO STDOUT always sends "\n" regardless of server platform. COPY FROM can handle lines ending with newlines, carriage returns, or carriage return/newlines. To reduce the risk of error due to un-backslashed newlines or carriage returns that were meant as data, COPY FROM will complain if the line endings in the input are not all alike.

• Vérace: I have dealt with this one in the answer - I hope you find it satisfactory. If not, let me know.

Answer 5

For me @Vérace's answer didn't maintain the column names, but assigned default names (f1, f2, etc.) instead. I am using PostgreSQL 9.1 with the JSON extension.

If you want to export the entire table, there is no need for a subquery. In addition, this will maintain the column names. I used the folowing query:

COPY (SELECT row_to_json(t) FROM fred as t) to '/home/pol/Downloads/software/postgres/inst/myfile';

• Vérace: It did maintain column names! CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20)); and the result: {"mary":2,"jimmy":43,"paulie":"asfasfasfd"} - field names are mary, jimmy, paulie... and NOT (f1, f2, etc.)...

Answer 6

For a generic (MySQL, Postgres, SQLite..) and free solution that you don't have to install any software for (except Docker), see https://github.com/function61/sql2json

Full disclosure: I wrote that software.

Answer 7

This is very easy to achieve with spyql:

$ psql -U my_user -h my_host -c "SELECT * FROM my_users" --csv my_db | spyql "SELECT * FROM csv TO json"
{"id": 1, "name": "David"}
{"id": 2, "name": "James"}
{"id": 3, "name": "Joana"}
{"id": 4, "name": "Hellen"}

We are using psql to export a query/table to CSV and then spyql to convert the CSV to json lines.

For the record, I created a sample table with the following SQL command:

CREATE TABLE my_users AS
SELECT *
FROM (VALUES (1, 'David'), (2, 'James'), (3, 'Joana'), (4, 'Hellen')) AS t(id, name);

Disclaimer: I am the author of spyql

Answer 8

Variant of @gunar-gessner 's answer with but with output file specified inline

\t
\a
select json_agg(t) FROM (SELECT * from table) t \g data.json

Answer 9

PG_CONNECTION_STR="postgresql://username:password@postgresql-host:port/dbname"
dump_pg_query_json() {
 file_name=1ドル; sql=2ドル ; shift 2;
 echo "SQL Query> ${sql}";
 echo "Output file> ${file_name}";
 psql -Atx ${PG_CONNECTION_STR} -c "COPY( SELECT ROW_TO_JSON(t, TRUE) FROM ( ${sql} ) t ) TO STDOUT WITH NULL AS ''" > "${file_name}"
}