MySQL's JSON Functions Verses PHP's JSON Functions

The MySQL JSON data type only accepts valid JSON documents. PHP has a handful of JSON functions but sadly json_encode does not provide what the database server wants. Lets start with a simple array.

$alpha = array('a' => 1, 'b' => 2, 'c' => 3);

This is a very simple associative array that we want to turn into a JSON doc with there elements, where 'a' is equal to 1 etcetera. This would look like this:

{"a": 1, "b": 2, "c": 3}

Using the built in PHP function json_encode we get this:

{"a":1,"b":2,"c":3}

Fantastic. We should be able to feed that into query, send it to the server, and be good to go. But when we try to feed that into MySQL it will return a syntax error. MySQL can be fussy about sending data in quotes, especially unescaped, in a query. So the associate array has to be serialized (fancy way to saw written out to a string) in a format MySQL can accept. That format means having the key in single quotes and the value unquoted.

$alphaz = "";
foreach ($alpha as $key => $value) {
        $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphaz,0,(strlen($alphaz) - 2)); /* strip last ,\n */

Next we use the MySQL JSON_OBJECT function to make sure it will pass muster with the server.

$query = "INSERT INTO foobar (mydata) VALUES (JSON_OBJECT($alphaz))";
echo "$query\n";
if ($result = $mysqli->query($query)) {
        echo "Inserted\n";
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}

Then we can check the database.

mysql> select * from foobar limit 1;
+--------------------------+
| mydata                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

Complete test program listing:

#!/usr/bin/php
 1, 'b' => 2, 'c' => 3);
echo var_dump($alpha);
echo "json_encoded: " . json_encode($alpha) . "\n";
echo "json_encoded HEX: " . json_encode($alpha,JSON_HEX_QUOT) . "\n";
$query0 = "SELECT JSON_OBJECT(" . json_encode($alpha) . ")";
echo "$query0\n";
if ($result = $mysqli->query($query0)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);

}


$alphaz = "";
foreach ($alpha as $key => $value) {
        $alphaz .= "'$key', $value, ";
}
$alphaz = substr($alphaz,0,(strlen($alphaz) - 2)); /* strip last ,\n */
$query1 = "SELECT JSON_OBJECT($alphaz)";
echo "$query1\n";

if ($result = $mysqli->query($query1)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
        echo "FOO!!!!\n";
}
$query2 = "INSERT INTO foobar (mydata) VALUES (JSON_OBJECT($alphaz))";
echo "$query2\n";
if ($result = $mysqli->query($query2)) {
        echo "Inserted\n";
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}
$query3 = "SELECT * FROM foobar";
if ($result = $mysqli->query($query3)) {
    $row = $result->fetch_row();
    printf("JSON is %s!\n", $row[0]);
} else {
        printf("Errormessage: %s\n", $mysqli->error);
}

$mysqli->close();
?>