Cannot import JSON file into MySQL



  • 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":
    


  • 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 "
    ";
    $print_r($airportsArray);
    echo "
    ";



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2