How to quote arrays in postgres copy statement to insert into JSON field

Question

I have some data that looks like this:

id,bo,c,url
1,"some text here", 22, "[{'id': 'NfKA3', 'u': 'https://somewebsite.com'}]"
2, "more text", 83, "[{'id': 'W3nAl', 'u': 'https://anotherwebsite.com'}]"
3, "even more text", 14, "[{'id': 'CyrMj', 'u': 'https://yetanotherwebsite.com'}]"

I'm trying to insert this data into a table that looks like this:

CREATE TABLE myTable(
id integer, 
body varchar, 
count, varchar, 
url JSON
);

Using this command:

\copy mytable FROM 'myData.csv' WITH DELIMITER ',' csv header;

However, I'm having trouble with the single quotes in the url field of the csv. I'm getting errors like this:

ERROR:  invalid input syntax for type json
DETAIL:  Token "'" is invalid.
CONTEXT:  JSON data, line 1: [{'...

Can someone show me the correct way to structure my \COPY command so as to be able to load the url field in the csv as a JSON?

If necessary I can do some pre-processing of the file at the command line.

Answer 1

JSON uses double quote " to delimite keys and strings:

[{"id": "NfKA3", "u": "https://somewebsite.com"}]

CSV uses double quote to escape double quotes "" means "

1,"some text here", 22, "[{""id"": ""NfKA3"", ""u"": ""https://somewebsite.com""}]"

Look at: https://stackoverflow.com/questions/17808511/properly-escape-a-double-quote-in-csv

Answer 2

Use a database script instead of a csv:

with v (id,bo,c,url) as 
(values 
( 1,'some text here', 22, cast('[{ "id": "NfKA3", "u": "https://somewebsite.com"}]' as json)),
( 2,'some text here', 83, cast('[{ "id": "W3nAl", "u": "https://somewebsite.com"}]' as json))
) 
insert into myTable select * from v;