MySQL Bugs: #86148: MySQL Workbench 6.3 problems importing JSON files into MySQL 5.7.12

Bug #86148	MySQL Workbench 6.3 problems importing JSON files into MySQL 5.7.12
Submitted:	1 May 2017 13:19	Modified:	9 Feb 2018 18:16
Reporter:	P vanBavel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	6.3, 6.3.9	OS:	Windows (7, 64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	import json exception

Description:
Table Data Import Wizard

Configure Import Settings
	Detected file format: json
	Columns: --- nothing visible
    	"Error
	 	Unhandled exception: 0"
INFORMATION DIALOG:
	Table Data Import
	"File not loaded properly, Please check the file and try again"

================ 	Content of file example.json 	================
{ "items": [
    {
      "key": "First",
      "value": 100
    },{
      "key": "Second",
      "value": false
    },{
      "key": "Last",
      "value": "Mixed"
    }
  ],
  "obj": {
    "number": 1.2345e-6,
    "enabled": true
  },
  "message": "Strings have to be in double-quotes."
}
====================================================================
The file structure of example.json  is 100% correct JSON.
====================================================================
Executing the following MySQL:

	SET @j = '{ "items": [
	    {
	      "key": "First",
	      "value": 100
	    },{
	      "key": "Second",
	      "value": false
	    },{
	      "key": "Last",
	      "value": "Mixed"
	    }
	  ],
	  "obj": {
	    "number": 1.2345e-6,
	    "enabled": true
	  },
	  "message": "Strings have to be in double-quotes."
	}';
	SELECT JSON_INSERT(@j, '$', '');

Produces:
# JSON_INSERT(@j, '$', '')
{"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
====================================================================
Executing the following MySQL (a couple of times) results in correct insertions of the JSON record.:

SET @j = '{ "items": [
	    {
	      "key": "First",
	      "value": 100
	    },{
	      "key": "Second",
	      "value": false
	    },{
	      "key": "Last",
	      "value": "Mixed"
	    }
	  ],
	  "obj": {
	    "number": 1.2345e-6,
	    "enabled": true
	  },
	  "message": "Strings have to be in double-quotes."
	}';
insert into test_table
VALUES(null , JSON_INSERT(@j, "$", ""));
# ID, JSON_Fld
1, {"An Array": [2, 3], "Just a value": 1, "JSON Structure": {"Array": [10, 20, 30]}}
2, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
3, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
4, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
5, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
===================================================================

How to repeat:
Table Data Import Wizard
Select File to Import
		<My file is called: example.json>
Select Destination and Options
Create New Table <Test>  and truncate befor import
Configure Import Settings
	Detected file format: json
	Columns: --- nothing visible
    	"Error
	 	Unhandled exception: 0"
INFORMATION DIALOG:
	Table Data Import
	"File not loaded properly, Please check the file and try again"

================ 	Content of file example.json 	================
{ "items": [
    {
      "key": "First",
      "value": 100
    },{
      "key": "Second",
      "value": false
    },{
      "key": "Last",
      "value": "Mixed"
    }
  ],
  "obj": {
    "number": 1.2345e-6,
    "enabled": true
  },
  "message": "Strings have to be in double-quotes."
}
====================================================================
The file structure of example.json  is 100% correct JSON.
====================================================================
Executing the following MySQL:

	SET @j = '{ "items": [
	    {
	      "key": "First",
	      "value": 100
	    },{
	      "key": "Second",
	      "value": false
	    },{
	      "key": "Last",
	      "value": "Mixed"
	    }
	  ],
	  "obj": {
	    "number": 1.2345e-6,
	    "enabled": true
	  },
	  "message": "Strings have to be in double-quotes."
	}';
	SELECT JSON_INSERT(@j, '$', '');

Produces:
# JSON_INSERT(@j, '$', '')
{"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
====================================================================
Executing the following MySQL (a couple of times) results in correct insertions of the JSON record.:

SET @j = '{ "items": [
	    {
	      "key": "First",
	      "value": 100
	    },{
	      "key": "Second",
	      "value": false
	    },{
	      "key": "Last",
	      "value": "Mixed"
	    }
	  ],
	  "obj": {
	    "number": 1.2345e-6,
	    "enabled": true
	  },
	  "message": "Strings have to be in double-quotes."
	}';
insert into test_table
VALUES(null , JSON_INSERT(@j, "$", ""));
# ID, JSON_Fld
1, {"An Array": [2, 3], "Just a value": 1, "JSON Structure": {"Array": [10, 20, 30]}}
2, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
3, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
4, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
5, {"obj": {"number": 0.0000012345, "enabled": true}, "items": [{"key": "First", "value": 100}, {"key": "Second", "value": false}, {"key": "Last", "value": "Mixed"}], "message": "Strings have to be in double-quotes."}
===================================================================

Hello P vanBavel,

Thank you for the report.
Observed this while importing valid json file using import wizard.

Thanks,
Umesh

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 8.0.0 release, and here's the
changelog entry:

Importing a JSON document with the Table Data Import Wizard was
unsuccessful.

Thank you for the bug report.