Description:
For large XML files there's the LOAD XML syntax (http://dev.mysql.com/doc/refman/5.7/en/load-xml.html) that allows splitting a repeating, large XML into a set or relational values.
IMHO, specially with the new JSON data type (and considering the lack of table expressions), it'll be really good to have a LOAD JSON command that will take a large JSON and split it into a set of relational rows containing either basic data types or JSON fragments
How to repeat:
1. With workbench, right click on a decently sized table, select "table data export wizard" and select a json target.
This produces a large json file containing an array of objects like this :
[
{ "col1": "data1", "col2": "data2" ...},
...
]
Suggested fix:
Considering the above implement e.g.
CREATE TABLE t1 (id int autoincrement, data json);
LOAD JSON 'out.json' into t1 (data) rows identified by '$.[*]' set data = $.*;
This will basically result in t1 being populated with the json array elements.
It will be great if this also supports a scalar expression instead of a file name, e.g.
LOAD JSON (SELECT json_col FROM json_table) into t1 (data) rows identified by '$.[*]' set data = $.*;
or a user variable at least:
LOAD JSON @json_temp_data INTO ....