Importing JSON into Mysql

Question

the problem that I am facing is importing JSON into Mysql. I have searched the interwebs, I have searched stackoverflow and probably even more sources, but could not find a single proper solution. Full disclosure here - I am not a PHP, SQL nor am I a JSON professional.

What I am trying to accomplish is fairly simple: Import this JSON file https://developers.facebook.com/tools/explorer/method=GET&path=245226895508982%2Ffeed%3Faccess_token%3D%3Caccess_token%3E= into a Mysql database. I understand that I need so somehow allocate the JSON data into columns and then curate the rows accordingly with the content. I do not need all of the data, but most of it. The data should later be echoed through a search query from a user.

My question is seems fairly simple - how can I do it?

P.S: I tried converting it into XML, but I do not have the sufficient rights to import the XML into the SQL database. Also the converter did not the best job.

Answer 1

You can export your json to csv : http://www.danmandle.com/blog/json-to-csv-conversion-utility/ or https://github.com/danmandle/JSON2CSV

Then :

LOAD DATA INFILE 'filepath/your_csv_file.csv' INTO TABLE tablename;

It should be okay for a oneshot.

More info for load data infile.

Answer 2

Like others have said, you are going to have to do a little bit of conversion to do what you're asking. Fortunately, it's not too difficult to have PHP iterate through the data and do most of the work for you. Here's a quick-and-dirty attempt:

// MySQL table's name
$tableName = 'my_table';
// Get JSON file and decode contents into PHP arrays/values
$jsonFile = '/path/to/file.json';
$jsonData = json_decode(file_get_contents($jsonFile), true);

// Iterate through JSON and build INSERT statements
foreach ($jsonData as $id=>$row) {
    $insertPairs = array();
    foreach ($row as $key=>$val) {
        $insertPairs[addslashes($key)] = addslashes($val);
    }
    $insertKeys = '`' . implode('`,`', array_keys($insertPairs)) . '`';
    $insertVals = '"' . implode('","', array_values($insertPairs)) . '"';

    echo "INSERT INTO `{$tableName}` ({$insertKeys}) VALUES ({$insertVals});" . "\n";
}

This will spit out a series of INSERT INTO my_table (...) VALUES (...); SQL statements that you can save to a .sql file and import into MySQL. With this method each INSERT can have different columns without causing problems.

Note: addslashes() isn't a very secure way of doing this, and something like real_escape_string() would be preferable, but this works without a DB connection assuming your JSON data is trustworthy.