0 records when importing a JSON file in mysql WorkBench

Question

I'm trying to import a .json into a database using MySQL workbench, but the "Table data import wizard" always end up with 0 records.

here's the first line of my .json:

{
 "Title":"Fort de Villiers",
 "Keywords":"Histoire",
 "Descripion":"Le fort de Villiers est un ancien fort du système défensif de Paris, situé sur la commune de Noisy-le-Grand, dans le département français de Seine-Saint-Denis. Construit à la fin du XIXᵉ siècle, il faisait partie du système Séré de Rivières, destiné a établir un rideau défensif autour de Paris.",
 "Images":["https://lh3.googleusercontent.com/proxy/J-7ut85_OfCJn33_EZcooBkGyjxGBXt5CifUJ_bGzHaZUHlT4XVYwNaIKpZ4hScn3ICcUMI12RZ4xzTxUGmN1LCK694hynag4zh68dLypDwQ_Gy9NwCIFweGTjkh3Wft4IMr0No1tFWbTQ6oBnmLgRaomsWzvoJrTa8=w296-h202-n-k-no","https://lh5.googleusercontent.com/p/AF1QipNs2wi72SiKVlLr1WHIPfAaATrXOfhswYWEIaOw=w296-h202-n-k-no","https://lh5.googleusercontent.com/p/AF1QipMIY16VgeazmwQAP7PB5biaq5p-9uE3OZ-zVYZB=w296-h202-n-k-no"],
 "Latitude":"48.833573",
 "Longitude":"2.5463771",
 "Map_url":"https://www.google.com/maps/place/Fort+de+Villiers/@48.833573,2.5463771,15z/data=!4m5!3m4!1s0x47e60e0d72beac57:0xd4b8549d2165eef7!8m2!3d48.8335733!4d2.5551319"
}

when i try to import it, i got those fields proposed:

Title : Text
Description : Text
Longitude : Double
Images : json
Map_url : Text
Keywords : Text
Latitude : Double

With some research i found out that it may be that the "text" columns countains characters that doesn't fill the requiring format, but i didn't manage to get the right format.

Do i need to modify my .json to get a correct utf8 format?

I'm actually using mysql 8.

Orelu

I'm trying to import a json file I literally just exported from another mysql instance, and I get the same "0 records" result on import :-( – Urina

Answer 1

Okay, I found it out. Issue was with data passed to multiple columns, such as passing null value because if you want to pass null value in Json, just omit the property, etc.

To debug which properties are causing the problem, try to import only necessary columns first, if it succeeds, add another property next time.

You can also do this by creating a trimmed version of your table.

Hope this helps everyone until they fix MySQL Workbench to show errors correctly.

Edit: Also I suggest using DBeaver to import data to mysql because Workbench had too many issues for me

Maribelmaribelle

Answer 2

During import don't change data type as Double, leave it as a default import data type (Text). Next use a sql script to update your specific column data type.

I have test with your data and attached screenshots as it's to guide.

My schema name: artbindu and your attached JSON table name: mapData

I'm creating table:

Leave all input field as it is:
(Latitude & Longitude both are Text format)
Next Import data from JSON file:

And Respective result should show, records are imported as follows:

Now, if check the table description, it will show all fields are text without images column.

desc mapData;

Now change datatype of columns Latitude & Longitude as follows:

ALTER TABLE artbindu.mapData 
    MODIFY Latitude DOUBLE,
    MODIFY  Longitude DOUBLE;
    
desc artbindu.mapData;

---

You may round-off Latitude & Longitude data up to n-decimal places:

update artbindu.mapData 
    set Latitude = round(cast(Latitude as double), 2),
        Longitude = round(cast(Longitude as double), 2);

For more details please check tutorial: Youtube
I hope your issue will fixed. Thank you...

Vey