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!).

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.

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)

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.

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';

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}"
}