Import in postgres json data in a csv file

Question

I'm trying to import into postgres a csv file containing the data for a table. One of the column of the table has jsonb type.

One line of my csv file contains something like

1,{"a":"b"}

Suppose the table has a schema

id              | smallint          | 
data            | jsonb             | 

If I try just to insert the data, everything works fine

INSERT INTO table VALUES (1, '{"a":"b"}');

Trying to import directly from the file with

COPY table FROM '/path/to/file.csv' DELIMITER ',' csv;

gives me the following error:

ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY availability, line 1, column services: "{a: b}"

I tried to quote the fields with ', with ", with \" and \', but nothing works..

Which is the correct syntax do do it?

Answer 1

The PostgreSQL COPY command is seldom ideal, but it often works. For reference there are better methods to figure this out than guessing.

CREATE TEMP TABLE baz AS
  SELECT 1::int, '{"a":"b"}'::jsonb;

This is your exact sample data. Now we can test different settings..

# COPY baz TO STDOUT;
1   {"a": "b"}

COPY baz TO STDOUT DELIMITER ',';
1,{"a": "b"}

You'll see that the above generates the exact data your questioning...

COPY baz TO '/tmp/data.csv' DELIMITER ',';

There is no problem. At least not with PostgreSQL 9.5.

CSV Mode

So where is your problem, it's with CSV-mode. Observe,

# COPY baz TO STDOUT;
1   {"a": "b"}
# COPY baz TO STDOUT CSV;
1,"{""a"": ""b""}"

You can see these two are different now. Let's try to load the non-CSV file in CSV mode which assumes the format that CSV mode generated above.

TRUNCATE baz;
COPY baz FROM '/tmp/data.csv' DELIMITER ',' CSV;
ERROR:  invalid input syntax for type json
DETAIL:  Token "a" is invalid.
CONTEXT:  JSON data, line 1: {a...
COPY baz, line 1, column jsonb: "{a: b}"

Now we error. The reason for that comes from RFC 4180

Each field may or may not be enclosed in double quotes (however some programs, such as Microsoft Excel, do not use double quotes at all). If fields are not enclosed with double quotes, then double quotes may not appear inside the fields.

1. So JSON RFC 4627 specifies an object's names in name/value pairs must be strings which require double quotes.
2. And CSV RFC 4180 specifies that if any double quotes are inside the field, then the whole field must be quoted.

At this point you have two options..

1. Don't use CSV mode.
2. Or, Escape the inner quotes.

So these would be valid inputs under the same options in CSV mode.

#COPY baz TO STDOUT DELIMITER ',' CSV ESCAPE E'\\';
1,"{\"a\": \"b\"}"

# COPY baz TO STDOUT DELIMITER ',' CSV;
1,"{""a"": ""b""}"

Answer 2

Found the solution, postgres uses " as an escaping character, so the correct format should be

{"""a""": """b"""}

Answer 3

As noted in other answers the CSV and the JSON specs (and probably the postgresql specs) are somewhat incompatible. To get them to stop fighting, at least in their simple forms, you have to escape things until they're an unreadable mess. Not using CSV mode is even worse as the COPY will die on anything that JSON has an issue with: embedded new lines, back-slashes or quote marks.

I had your exact same problem only with a much more complex input: a bunch of ENUM types, integers and complex JSON fields. To wit:

create table messages( a blab, b integer, c text, d json, e json);

where the typical JSONs would be {"default":"little","sms":"bigger"} and ["name","number"]. Try importing a pile of those with a COPY command: if the commas inside the JSON don't get you the quote marks will! I spend hours on this until i found this fine blog post which pointed out the causes of the problem and the options you need to get out of it.

Basically you need to change the delimiter and quote fields to something that you can guarantee won't be in your JSON data. In my case I can guarantee a lot so I can just go

COPY messages( a, b, c, d, e) from stdin  csv quote '^' delimiter '|';
malfunction|5|La la la|{"default":"little","sms":"bigger"}|["name","number"]

Nice and readable, easy to get to with some minor character substitution in your favourite text mangler, and no escaping of no nothing! If you can't guarantee that the characters used above can't be in your JSON then you can use the rather wacky e'\x01' and e'\x02' as the JSON spec deems them totally illegal. Not quite as readable and so forth but punctiliously correct.

Note that embedded new-lines, as some JSON generators tend to emit for readability purposes, are still 'no-no's so you have to filter them out of your JSON.

Answer 4

I tried using spyql do to the job and it worked fine. spyql can generate INSERT statements that you can use to import the data into your db. spyql is based on python and the standard csv lib would not work well if your jsons have several keys, since the delimiter is the same. Therefore, the approach I suggest is manually splitting each line into two columns:

$ spyql -Otable=table_name "SELECT col1.split(',',1)[0] AS id, col1.split(',', 1)[1] AS data FROM text TO sql" < sample.csv 
INSERT INTO "table_name"("id","data") VALUES ('1','{"a":"b"}'),('2','{"a":"foo", "b": 2}');

This assumes only 2 columns as in the example, and no header row.

Importing the data into postgres could be done by piping the output into psql:

$ spyql -Otable=table_name "SELECT col1.split(',',1)[0] AS id, col1.split(',', 1)[1] AS data FROM text TO sql" < sample.csv | psql -h my_host -U my_user my_db

You would have to create the table in the database beforehand, but that's it.

Disclosure: I am the author of spyql

Answer 5

For me the answer was to put double quotes around the entire JSON object and change each double quote within the object to two double quotes. So basically this

{ "a": 1, "b": "two" }

became this:

"{ ""a"": 1, ""b"": ""two"" }"