5

I have following example json file and I'm trying to populate a MySQL table with it. Since MySQL 5.7 supports json as a native datatype I thought this shouldn't be a problem, but MySQL's json validator seems to have a problem with the line breaks inside the file. As soon as I get rid of all line breaks and write my example file in one line, it works perfectly. 

[
{
    "somestuff": [
        {
            "field1": "val1",
            "field2": 17,
            "field3": 27,
            "field4": 42,
            "field5": 73
        },
        {
            "field1": "val2",
            "field2": 3,
            "field3": 12,
            "field4": 13,
            "field5": 100
        }
    ],
    "field0": "image",
    "path": "some path"
}
]

I know json natively has problems with strings containing backslashes, but even when writing any kind of word instead of the path, the MySQL json validator still can't completely read the json file and just stops before the first line break. I still get following error:

Error Code: 3140. Invalid JSON text: "Invalid value." at position 1 in value (or column) '['.

when using following query:

-- create test table for json data
CREATE TABLE IF NOT EXISTS jsons_test(
       annotation_id INT AUTO_INCREMENT NOT NULL UNIQUE, 
        json_data json);
LOAD DATA LOCAL INFILE 'C:\\some\\path\\test.json'
INTO table json_test(json_data);

I guess this most probably happens because of the line break encoding, but I still don't know if there's any workaround to solve it.

UPDATE: I found a solution on my own... By adding LINES TERMINATED BY '\\n' I could successfully populate the table with the json data and make use of the advantages of MySQL's new json support.

user3168930
  • 81
  • 1
  • 7
  • Unless I'm missing something, `load data infile` expects CSV. I have impression you only have the raw JSON :-? – Álvaro González Apr 18 '16 at 14:55
  • According to some websites I found, it seems to work. (http://www.databasejournal.com/features/mysql/working-with-json-data-in-mysql-7.html or http://dasini.net/blog/2015/11/17/30-mins-with-json-in-mysql/ ). It also works perfectly fine when deleting all line breaks, leaving a one-line-json-file. So the command doesn't seem to be the problem :/ – user3168930 Apr 18 '16 at 15:58
  • Besides, the fact that I even get an error message related to the json-syntax proves that the sql command actually is able to read it as a json file and even try to validate it.. – user3168930 Apr 18 '16 at 16:06
  • Please read those articles carefully. The first one appears to load a single column CSV file with several rows. That's far from being JSON. – Álvaro González Apr 18 '16 at 16:06
  • CSV is not XML. MySQL will parse row by row and do its best with incomplete data. – Álvaro González Apr 18 '16 at 16:09
  • I do know that...it still doesn't answer my question, so I'll ask it once more. Is there any way to load data from a json file into a MySQL table without having to delete all line breaks? (and yes I do know json actually doesn't even like line breaks, it just makes it readable for us people...but it's the output I get from another software) – user3168930 Apr 18 '16 at 16:43
  • I have the impression that I've failed to explain my point about CSV. If you type random words in a file you often obtain a completely valid one-column CSV file by pure chance because CSV is as simple as that. That illusion breaks as soon as data itself contains characters that have a special meaning in CSV (such as line feeds). If you cannot compose properly encoded CSV you'll have to find another tool. – Álvaro González Apr 18 '16 at 16:52

1 Answers1

3

I found a solution for my problem: see UPDATE

user3168930
  • 81
  • 1
  • 7