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.