Grab JSON DATA from MySQL and Update It

So lets get some data from a JSON column from a table in a MySQL database and update it. JSON data is is simply a column like a REAL, INTEGER, CHAR, or VARCAHR. So fetching the data from a JSON column is the same as any other column. The same database we have has two columns -- a integer for a identification number and a second for JSON data.

Fetch Data

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "SELECT * FROM stooge";
$result = $mysqli->query($query1);
    while($row = $result->fetch_assoc()) {
        printf("%d - %s\n", $row["id"], $row["info"]);
}

$mysqli->close();
?>

When the program executes we see the following:

1 - {"name": "Moe Howard"}
2 - {"name": "Larry Fine"}
3 - {"name": "Shemp Howard"}

So lets add another record

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "INSERT INTO stooge VALUES (4,JSON_OBJECT('name','Curly Howard'))";
print "$query1\n";
if(!$result = $mysqli->query($query1)) {
 printf("Error: %s\n", $mysqli->error);
}

$mysqli->close();
?>

Please note that the example programs are very simple and from here on out they will skip some obvious error checking. I am trying to show the concepts of using the MySQL JSON data type and not the role of proper error checking which you should have ingrained in you PERIOD. I am going for brevity in the examples and not teaching proper programming practices.

mysql -u root -p test -e "SELECT * FROM stooge\g";
Enter password: 
+------+---------------------------+
| id   | info                      |
+------+---------------------------+
|    1 | {"name": "Moe Howard"}   |
|    2 | {"name": "Shemp Howard"} |
|    3 | {"name": "Larry Fine"}   |
|    3 | {"name": "Curly Howard"} |
+------+---------------------------+

So how do we get just the name. We can use JSON_EXTRACT in two ways to get the data. We can use JSON_EXTRACT directly as in the example code below or the short hand SELECT info->"$.name" AS Name FROM stooge; The arrow operator is shorthand for JSON_EXTRACT and there is no penalty for using one over the other.

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "SELECT JSON_EXTRACT(info, '$.name') AS Name  FROM stooge";
$result = $mysqli->query($query1);
    while($row = $result->fetch_assoc()) {
        printf("%s\n", $row["Name"]);
}

$mysqli->close();
?>

And we get the names as so:

"Moe Howard"
"Larry Fine"
"Shemp Howard"
"Curly Howard"

Don't like the double quotes? Wrap JSON_UNQUOTE around the core of the select as in 'SELECT JSON_UNQUOTE(info->"$.name") AS Name FROM stooge' to remove them.

A new Key/Value Pair

Lets add a new Key/Value pair to our data. Each of the JSON documents will get a new Key names 'job' and a value inserted. We have to tell the serve which document we are using (you can have more than one JSON column or document per table), the name of this Key, and a value. We use "UPDATE stooge SET info = JSON_SET(info,'$.job','Comic')" to tell the server we are adding a new key to the info document/column, naming it job and for this example all the records are given the same job value.

!/usr/bin/php
?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "UPDATE stooge SET info = JSON_SET(info,'$.job','Comic')";
$result = $mysqli->query($query1);

$mysqli->close();
?>

The data:

mysql> select * from stooge;
+------+------------------------------------------+
| id   | info                                     |
+------+------------------------------------------+
|    1 | {"job": "Comic", "name": "Moe Howard"}   |
|    2 | {"job": "Comic", "name": "Larry Fine"}   |
|    3 | {"job": "Comic", "name": "Shemp Howard"} |
|    4 | {"job": "Comic", "name": "Curly Howard"} |
+------+------------------------------------------+
4 rows in set (0.00 sec)

Note that MySQL alphabetizes the keys.

Replacement Job

Let's change Moe Howard's job. We will use the LIMIT clause on our SQL to update only one entry (and Moe is the first entry in this data set).

#!/usr/bin/php
<?php
$mysqli = new mysqli("localhost", "root", "hidave", "test");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query1 = "UPDATE stooge SET info = JSON_REPLACE(info,'$.job','Head Stooge') LIMIT 1";
print "$query1\n";

$result = $mysqli->query($query1) ;


$mysqli->close();
?>

And we update only the first record:

mysql> select * from stooge;
+------+----------------------------------------------+
| id   | info                                         |
+------+----------------------------------------------+
|    1 | {"job": "Head Stooge", "name": "Moe Howard"} |
|    2 | {"job": "Comic", "name": "Larry Fine"}       |
|    3 | {"job": "Comic", "name": "Shemp Howard"}     |
|    4 | {"job": "Comic", "name": "Curly Howard"}     |
+------+----------------------------------------------+
4 rows in set (0.00 sec)

Next time we will dig deeper into the MySQL JSON Functions.