Bug #79209 Need a LOAD JSON statement
Submitted: 10 Nov 2015 16:26 Modified: 16 Nov 2015 7:10
Reporter: Georgi Kodinov Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7.10 OS:Any
Assigned to: CPU Architecture:Any

[10 Nov 2015 16:26] Georgi Kodinov
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 ....
[16 Nov 2015 7:10] MySQL Verification Team
Hello Joro,

Thank you for the feature request!

Thanks,
Umesh