MySQL JSON Meta Data Fuctions

tháng 2 12, 2016

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.