JSON_TABLE Part 2

The JSON_TABLE function has some very interesting uses. JSON data is great for schemaless jobs but what about when you need to pretend you have a schema and/or need to create tables based on values in a JSON document.

Existence and Defaults

Let start with some simple data:

mysql> SELECT * FROM t1;
+-----+--------------------------+
| _id | doc                      |
+-----+--------------------------+
|   1 | {"x": 0, "name": "Bill"} |
|   2 | {"x": 1, "name": "Mary"} |
|   3 | {"name": "Pete"}         |
+-----+--------------------------+
3 rows in set (0.00 sec)

We have three documents and you will notice that the third record is missing a 'x' key/value pair. We can use JSON_TABLE to provide a value when that value is missing. For this example a missing value of 'x' is given a value of 999.

mysql> select * from t1, json_table(doc,"$" columns 
   (xHasValue int path "$.x" default '999' on empty, 
    hasname char(5) exists path "$.name", 
    mojo char(5) exists path "$.mojo")) as t2;
+-----+--------------------------+-----------+---------+------+
| _id | doc                      | xHasValue | hasname | mojo |
+-----+--------------------------+-----------+---------+------+
|   1 | {"x": 0, "name": "Bill"} |         0 | 1       | 0    |
|   2 | {"x": 1, "name": "Mary"} |         1 | 1       | 0    |
|   3 | {"name": "Pete"}         |       999 | 1       | 0    |
+-----+--------------------------+-----------+---------+------+
3 rows in set (0.00 sec)

mysql> 

Do we have that data?

We can also use the exists qualifier to test the existence of a key. The last two line in the query exists checks for a name which does exists and reports a '1'. And check for mojo which does not exist and reports a '0'. We can of course use these binary fields in our query.

mysql> select * from t1, json_table(doc,"$" columns 
   (xHasValue int path "$.x" default '999' on empty, 
    hasname char(5) exists path "$.name", 
    mojo char(5) exists path "$.mojo")) as t2 
    WHERE hasname=1 and xHasValue=1;
+-----+--------------------------+-----------+---------+------+
| _id | doc                      | xHasValue | hasname | mojo |
+-----+--------------------------+-----------+---------+------+
|   2 | {"x": 1, "name": "Mary"} |         1 | 1       | 0    |
+-----+--------------------------+-----------+---------+------+
1 row in set (0.01 sec)

mysql> 

JSON_TABLE

JSON data is a wonderful way to store data without needing a schema but what about when you have to yank that data out of the database and apply some sort of formatting to that data?  Well, then you need JSON_TABLE.

JSON_TABLE takes free form JSON data and applies some formatting to it.  For this example we will use the world_x sample database's countryinfo table.  What is desired is the name of the country and the year of independence but only for the years after 1992.  Sound like a SQL query against JSON data, right? Well that is exactly what we are doing.

We tell the MySQL server that we are going to take the $.Name and $.IndepYear key's values from the JSON formatted doc column in  the table, format them into a string and a integer respectively, and alias the key value's name to a table column name that we can use for qualifiers in an SQL statement.

mysql> select country_name, IndyYear from countryinfo, 
json_table(doc,"$" columns (country_name char(20) path "$.Name",
           IndyYear int path "$.IndepYear")) as stuff 
where IndyYear > 1992;
+----------------+----------+
| country_name   | IndyYear |
+----------------+----------+
| Czech Republic |     1993 |
| Eritrea        |     1993 |
| Palau          |     1994 |
| Slovakia       |     1993 |
+----------------+----------+
4 rows in set, 67 warnings (0.00 sec)



mysql>

So what else can JSON_TABLE do? How about default values for missing values? Or checking that a key exists in a document. More on that next time. For now if you want to try MySQL 8.0.3 with JSON_TABLES, you need to head to Labs.MySQL.COM to test this experimental feature.