Cannot import JSON file into MySQL

Question

I am trying to import a JSON file into a MySQL database. It is FAA data of all the airports in the US. https://services6.arcgis.com/ssFJjBXIUyZDrSYZ/arcgis/rest/services/US_Airport/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json

It looks like it has field definitions first and it is confusing DBeaver and MySQL Workbench. Any ideas on how to get this into a database table? Here is what the first few rows look like. {"OBJECTID":1 is the start of the first column of the first row of data. How do I get past all the data definition stuff at the beginning? It looks like this is a description of the fields https://services6.arcgis.com/ssFJjBXIUyZDrSYZ/ArcGIS/rest/services/Pending_Airports/FeatureServer/0

{"objectIdFieldName":"OBJECTID","uniqueIdField":{"name":"OBJECTID","isSystemMaintained":true},"globalIdFieldName":"","geometryType":"esriGeometryPoint","spatialReference":{"wkid":4326,"latestWkid":4326},"fields":[{"name":"OBJECTID","type":"esriFieldTypeOID","alias":"OBJECTID","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"GLOBAL_ID","type":"esriFieldTypeString","alias":"GLOBAL_ID","sqlType":"sqlTypeOther","length":50,"domain":null,"defaultValue":null},{"name":"TYPE_CODE","type":"esriFieldTypeInteger","alias":"TYPE_CODE","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"IDENT","type":"esriFieldTypeString","alias":"IDENT","sqlType":"sqlTypeOther","length":254,"domain":null,"defaultValue":null},{"name":"CHANNEL","type":"esriFieldTypeString","alias":"CHANNEL","sqlType":"sqlTypeOther","length":254,"domain":null,"defaultValue":null},{"name":"NAS_USE","type":"esriFieldTypeSmallInteger","alias":"NAS_USE","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"CLASS_TXT","type":"esriFieldTypeString","alias":"CLASS_TXT","sqlType":"sqlTypeOther","length":12,"domain":null,"defaultValue":null},{"name":"NAME_TXT","type":"esriFieldTypeString","alias":"NAME_TXT","sqlType":"sqlTypeOther","length":100,"domain":null,"defaultValue":null},{"name":"CITY","type":"esriFieldTypeString","alias":"CITY","sqlType":"sqlTypeOther","length":254,"domain":null,"defaultValue":null},{"name":"STATE","type":"esriFieldTypeString","alias":"STATE","sqlType":"sqlTypeOther","length":254,"domain":null,"defaultValue":null},{"name":"COUNTRY","type":"esriFieldTypeString","alias":"COUNTRY","sqlType":"sqlTypeOther","length":254,"domain":null,"defaultValue":null},{"name":"STATUS","type":"esriFieldTypeString","alias":"STATUS","sqlType":"sqlTypeOther","length":254,"domain":null,"defaultValue":null},{"name":"REMARKS","type":"esriFieldTypeString","alias":"REMARKS","sqlType":"sqlTypeOther","length":254,"domain":null,"defaultValue":null},{"name":"AK_LOW","type":"esriFieldTypeSmallInteger","alias":"AK_LOW","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"AK_HIGH","type":"esriFieldTypeSmallInteger","alias":"AK_HIGH","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"US_LOW","type":"esriFieldTypeSmallInteger","alias":"US_LOW","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"US_HIGH","type":"esriFieldTypeSmallInteger","alias":"US_HIGH","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"US_AREA","type":"esriFieldTypeSmallInteger","alias":"US_AREA","sqlType":"sqlTypeOther","domain":null,"defaultValue":null},{"name":"PACIFIC","type":"esriFieldTypeSmallInteger","alias":"PACIFIC","sqlType":"sqlTypeOther","domain":null,"defaultValue":null}],"exceededTransferLimit":true,"features":[{"attributes":{"OBJECTID":1,"GLOBAL_ID":"DECD1B7B-F419-4D71-97AE-25BC91363C1B","TYPE_CODE":4,"IDENT":"ADK","CHANNEL":null,"NAS_USE":1,"CLASS_TXT":"HW/DME","NAME_TXT":"MOUNT MOFFETT","CITY":"ADAK ISLAND","STATE":"AK","COUNTRY":"UNITED STATES","STATUS":"IFR","REMARKS":"DME CHANNEL 087X IS PAIRED WITH VHF FREQ 114.0.||DME UNUSBL 080-105 BYD 27 NM, 105-115, 115-155 BYD 27 NM, 155-225, 225-290 BYD 27 NM, 290-340, 340-055 BYD 20 NM.","AK_LOW":1,"AK_HIGH":1,"US_LOW":0,"US_HIGH":0,"US_AREA":0,"PACIFIC":0},"geometry":{"x":-176.67602422142775,"y":51.87189606056274}},{"attributes":

Answer 1

There was nothing wrong the with the JSON format. Just a lot of fields descriptions and other information. I used this code to see the where the data actually was

$json = file_get_contents('airports.json');
$airportsArray = json_decode($json, true);
echo "<pre>";
$print_r($airportsArray);
echo "</pre>";

Answer 2

JSON data import using MySQL Workbench

I am using MySQL and the Workbench, both with versions 8.0.34 and an example JSON document in a file, eg., "myfile.json". This has one object in an array). This will create one row in the target table. With two or more objects in the JSON array, more rows will be created respectively.

myfile.json:

[
  {
   "name":"Jerry",
   "age":10,
   "likes":[
      "skiing",
      "cheese"
   ]
  }
]

In the MySQL Workbench, navigate and follow these steps to import the JSON from the file. I am using the option to create a new table from the data in the file.

From Object Browser and Editor Navigator navigate and perform these actions:

Navigator Schemas Tab -> Select the schema -> Select the Tables
Right-click on the Tables for a context-menu
Select Table Data Import Wizard
Select or specify the file path of the JSON file to import -> click Next
Select Destination - Select Create new table (and an existing database) -> click Next
Configure Import Settings - No action needed -> click Next
Import Data -> click Next
Import Results - See a message that n records are imported into db.table -> click FINISH

Back in the Object Browser and Editor Navigator, refersh the Schemas or Tables. View or query the newly created table with data.

Note that this created a table with following definition:

CREATE TABLE `mytable` (
  `name` text,
  `age` int DEFAULT NULL,
  `likes` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

---

Import Using the JSON from the Question post:

The document from the link

https://services6.arcgis.com/ssFJjBXIUyZDrSYZ/arcgis/rest/services/US_Airport/FeatureServer/0/query?where=1%3D1&outFields=*&outSR=4326&f=json

This is a valid JSON. I saved it to a file as one document in an array (as shown in the example above).

I imported the file using the above steps to create a new table and a row of data successfully.

Answer 3

You can troubleshoot it.

1. Make sure MySQL version is newer than 5.7.8.

2. Check the validity of the JSON file

3. Check the field format