How to load JSON string into JSON column

Question

I have MySQL table that I like to populate with load data infile.

One of the column is a JSON type, to which I want to upload an entire json string. The problem is that it seems really hard to upload the json string, where I get:

Invalid utf8 character string: '{'

A row from my CSV file:

25621943694,john,johnny,ACTIVE,organization,1234,{\”name\”: \”cicd-prod\”},2016-10-22 16:57:36

My load data statement:

LOAD DATA LOCAL INFILE 'test123.csv'
INTO TABLE projects 
FIELDS TERMINATED BY ',' (project_number,project_id,project_name,lifecycle_state,parent_type,parent_id,raw_project,create_time);

You shouldn't have curly quotes in the JSON, they should be ASCII double quotes. — Barmar
– Barmar, Commented Feb 9, 2017 at 21:46
If you are trying to store the actual JSON string as your MySQL column it might be a good idea, if you are able, to modify the incoming JSON so the fields are delimited by single quotes. That way you can store the entire JSON block as a MySQL string. i.e. ... 1234, "{ 'name':'cicd-prod'}",2016-10-22 16:57:36 — NastyDiaper
– NastyDiaper, Commented Feb 9, 2017 at 21:50

Schwern · Accepted Answer · 2017-02-09 22:13:27Z

1

In JSON, according to the ECMA standard, "a string is a sequence of Unicode code points wrapped with quotation marks (U+0022)."

Your CSV is not using U+0022. It's using U+0201D - Right Double Quotation Mark. It's not even using them correctly as I-cannot-put-enough-smart-quotes-around-“smart” quotes; its using the right quote for both the right and left sides.

Those quotes have to be converted back to a boring old U+0022 - Quotation Mark. And you need to have a chat with whomever generated that CSV. It's also possible an overzealous Office app mangled it.

answered Feb 9, 2017 at 22:13

Schwern

167k28 gold badges225 silver badges370 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to load JSON string into JSON column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related