Wednesday, February 24, 2016

MySQL JSON Keys

Continuing from the last entry on using MySQL's new JSON data type , let us take a look at the keys in our sample database. Use JSON_KEYS to get a lit of the Keys withing the document.
mysql> SELECT JSON_KEYS(info) FROM stooge;
+-----------------+
| json_keys(info) |
+-----------------+
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
| ["job", "name"] |
+-----------------+
4 rows in set (0.00 sec)
So how many records have a 'job' key?
mysql> select  JSON_CONTAINS_PATH(info,'all','$.job') from stooge;
+----------------------------------------+
| JSON_CONTAINS_PATH(info,'all','$.job') |
+----------------------------------------+
|                                      1 |
|                                      1 |
|                                      1 |
|                                      1 |
+----------------------------------------+
4 rows in set (0.00 sec)
The 1 means the record has a 'job' key and a 0 would indicate a lack. Not too interesting in this case as all the JSON docs have the same keys. But how do you gran one particular key/value, also know as a object? If we know the object(s)/ record(s) sought contain something to search on we pass that to JSON_CONTAINS.
SELECT * FROM stooge WHERE JSON_CONTAINS(info,'{"job": "Head Stooge"}');
My history of fat fingering brackets, braces, and single/double quotes is pretty long and is it not cheating to use JSON_OBJECT to help here.
mysql> SELECT * FROM stooge WHERE JSON_CONTAINS(info,JSON_OBJECT('job','Head Stooge'));
+------+----------------------------------------------+
| id   | info                                         |
+------+----------------------------------------------+
|    1 | {"job": "Head Stooge", "name": "Moe Howard"} |
+------+----------------------------------------------+
1 row in set (0.00 sec)
Now up to this point we have not extracted the JSON data into a generated column for SQL compatible indexes. If we look at the EXPLAIN for the last query it tells use we had a full table scan. And full table scans are not efficient.
mysql> EXPLAIN select JSON_SEARCH(info,'Comic','job') from stooge\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stooge
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> 

Monday, February 22, 2016

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.

Monday, February 15, 2016

MySQL JSON Functions to Create Values

MySQL 5.7's new JSON data type has three functions to help you make sure your data is a valid utf8mb4 character set JSON document. They are JSON_ARRAY, JSON_QUOTE, and JSON_OBJECT. (You can also obtain JSON values by casting values of other types to the JSON type using CAST(value AS JSON)) What is the big difference? And when would you use one over another?

JSON_ARRAY takes a string as input and returns a JSON array with the values from the string.

mysql> select JSON_ARRAY('')
+----------------+
| JSON_ARRAY('') |
+----------------+
| [""]           |
+----------------+
1 row in set (0.01 sec)

mysql>  SELECT JSON_ARRAY('Foo', 42, now());
+-------------------------------------------+
| JSON_ARRAY('Foo', 42, now())              |
+-------------------------------------------+
| ["Foo", 42, "2016-02-15 07:31:56.000000"] |
+-------------------------------------------+
JSON_QUOTE takes the string given as input and wraps it with double quotes.
mysql> SELECT JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}');
+----------------------------------------+
| JSON_QUOTE('{foo,1,{0,1,2},"FOOBAR"}') |
+----------------------------------------+
| "{foo,1,{0,1,2},\"FOOBAR\"}"           |
+----------------------------------------+
1 row in set (0.00 sec)
And finally JSON_OBJECT takes a list of key/value pairs and returns a JSON object containing those pairs. It will error if the number of arguments is odd or a key is named NULL.
 SELECT JSON_OBJECT('a',1,'bb',345);

+-----------------------------+
| JSON_OBJECT('a',1,'bb',345) |
+-----------------------------+
| {"a": 1, "bb": 345}         |
+-----------------------------+
1 row in set (0.00 sec)
So you would use JSON_OBJECT for creating JSON Objects (key/value pairs), JSON_ARRAY for creating arrays, some of which could be including in a bigger JSON Document, and JSON_QUOTE to escape embedded quotes. Next time we will look at the MySQL JSON Functions to modify JSON data.

Friday, February 12, 2016

MySQL JSON Meta Data Fuctions

Lost post covered the use of the MySQL JSON functions to feed data into the database server and why the PHP json_encode did not provide what was needed. THis time we will look how to examine the data once it is in a JSON column. We started with a simple associative array and fed it into the database.
mysql> SELECT * FROM foobar;
+--------------------------+
| mydata                   |
+--------------------------+
| {"a": 1, "b": 2, "c": 3} |
+--------------------------+
1 row in set (0.00 sec)

MySQL 5.7 has a group of JSON functions for looking at attributes. See the MySQL Manual 12.16.5 Functions That Return JSON Value Attributes. And they do not always work in an intuitive fashion! JSON_DEPTH returns the maximum depth of a JSON document. It will return a NULL if passed a NULL and return an error if the document is not valid JSON. An empty array, object or scalar value has a depth of 1. Non empty arrays or objects with elements or members of depth 1 returns a 2. Beyond that it returns the number of the number of the depth of the JSON document. Clear as mug, right? So lets look at our example array now fed into MySQL.
mysql> SELECT JSON_DEPTH('{"a": 1, "b": 2, "c": 3}');
+----------------------------------------+
| json_depth('{"a": 1, "b": 2, "c": 3}') |
+----------------------------------------+
|                                      2 |
+----------------------------------------+
1 row in set (0.00 sec)
Still not a lot of help. So lets go simpler.
mysql> select json_depth('[]'),json_depth('[1]');
+------------------+-------------------+
| json_depth('[]') | json_depth('[1]') |
+------------------+-------------------+
|                1 |                 2 |
+------------------+-------------------+
1 row in set (0.00 sec)
So the empty array [] has a depth of 1. An array with a value inside it has a depth of two. But it helps if we go even simpler. Let look at a cut down version of the test array and then the JSON doc.
mysql> select json_depth('{"a": 1}');
+------------------------+
| json_depth('{"a": 1}') |
+------------------------+
|                      2 |
+------------------------+
1 row in set (0.00 sec)
The JSON doc itself looks like
{
  "a": 1
}
Now it gets a little clearer! The document has a depth of two from the two elements, a and 1. So lets go a little overboard. Here is an example from http://json-schema.org/example1.html
{
    "id": 1,
    "name": "A green door",
    "price": 12.50,
    "tags": ["home", "green"]
}
mysql> select JSON_DEPTH('{"id": 1, "name": "A green door", "price": 12.50,"tags": ["home", "green"]}'); 
+---------------------------------------------------------+
| JSON_DEPTH('{
    "id": 1,
    "name": "A green door",
    "price": 12.50,
    "tags": ["home", "green"]}') |
+----------------------------------------------------------+
|                                                        3 |
+----------------------------------------------------------+
Run again without the 'tags' line and the depth is 2. So this is a case where I have to draw out the doc to understand the depth. JSON_LENGTH returns the length of the doc or of the element in the document you are referencing. The length of a scalar is one, the length of an array is the number of elements, the length of an object is the number of objects, and be cautious as it doesn't count nested objects or arrays. mysql> select json_length('{"a": 1, "b": 2, "c": 3}'); +-----------------------------------------+ | json_length('{"a": 1, "b": 2, "c": 3}') | +-----------------------------------------+ | 3 | +-----------------------------------------+ 1 row in set (0.00 sec) mysql> select json_length('{"a": 1, "b": 2, "c": 3, "d": 4}'); +-------------------------------------------------+ | json_length('{"a": 1, "b": 2, "c": 3, "d": 4}') | +-------------------------------------------------+ | 4 | +-------------------------------------------------+ 1 row in set (0.00 sec) An example were a single item in the doc is tested for length.
mysql> select json_length('{"a": 1, "b": 2, "c": 3}','$.a');
+-----------------------------------------------+
| json_length('{"a": 1, "b": 2, "c": 3}','$.a') |
+-----------------------------------------------+
|                                             1 |
+-----------------------------------------------+
1 row in set (0.00 sec)

mysql> 
JSON_TYPE returns the a string telling the type of the item. mysql> select json_type('{"a": 1, "b": 2, "c": 3}');
+---------------------------------------+
| json_type('{"a": 1, "b": 2, "c": 3}') |
+---------------------------------------+
| OBJECT                                |
+---------------------------------------+
1 row in set (0.00 sec)
And you can drill down to individual elements.
mysql> select json_type(json_extract('{"a": 1, "b": 2, "c": 3}','$.a'));
+-----------------------------------------------------------+
| json_type(json_extract('{"a": 1, "b": 2, "c": 3}','$.a')) |
+-----------------------------------------------------------+
| INTEGER                                                   |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> 
And finally JSON_VALID returns a 1 if you have a valid JSON document. VEry handy if you have doubts for testing the data before trying to shove it into the database.
mysql> select json_valid('{"a": 1, "b": 2, "c": 3}');
+----------------------------------------+
| json_valid('{"a": 1, "b": 2, "c": 3}') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+
1 row in set (0.00 sec)
Next time we will look at more MySQL 5.7 JSON functions and see how they can be used by a PHP code for world domination betterment of the world.

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();
?>

Thursday, February 11, 2016

MySQL's JSON Data Type

MySQL 5.7 features a native JSON datatype. So just like a INT, CHAR, or other data type, you can now store valid JSON documents in a MySQL database. Previously you could put JSON formatted data into a CHAR, a TEXT, or similar data type. But it was a real pain to search this JSON data for items contained in them. But now you can store the JSON and there is a list functions to allow full access to this data in the column.
shell$ mysql -u root -p test
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE jdata (mydata JSON);
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO jdata (mydata) VALUES (JSON_OBJECT('name', 'Dave', 'height', 65));
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM jdata;
+-------------------------------+
| mydata                        |
+-------------------------------+
| {"name": "Dave", "height": 65} |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_EXTRACT(mydata,'$.name') FROM jdata;
+-------------------------------+
| JSON_EXTRACT(mydata,'$.name') |
+-------------------------------+
| "Dave"                        |
+-------------------------------+
1 row in set (0.00 sec)

mysql> 

Please note the above SELECT statement. SQL is designed to pull data from columns but JSON data is in documents so we now need functions to pull keys from documents in that column. JSON_EXTRACT is looking in the mydata column and we use the $.name to say 'we want the value of this key in the current document'. Over the next few blog posts I will cover these functions and more.