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

up vote 33 down vote accepted

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

  • 1
    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. – offby1 Apr 30 '16 at 16:44
  • 3
    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). – Adam Gent Aug 25 '16 at 2:37
  • READ @AdamGent 's answer below to solve the backslash issue – FacePalm Feb 8 '17 at 7:29
  • @offby1 - take a look at my revised answer which I believe answers your issue! – Vérace Aug 30 '17 at 20:24
  • @AdamGent - take a look at the use of REGEXP_REPLACE which obviates your issue! – Vérace Aug 30 '17 at 20:25

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.

  • 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 – Peter Krauss Nov 5 '17 at 12:28

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.

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';
  • 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.)... – Vérace Nov 30 '17 at 20:58

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.