Structuring Your Unstructured JSON data

The world seems awash in unstructured, NoSQL data, mainly of the JSON variety.  While this has a great many benefits as far as data mutability and not being locked into a rigid structure there are some things missing that are common in the structured world of SQL databases.

What if there was a way to take this unstructured NoSQL JSON data and cast it, temporarily, into a structured table?  Then you could use all the processing functions and features found in a relation database on you data.  There is a way and it is the JSON_TABLE function.

JSON_TABLE

You can find the documentation for JSON_TABLE here  but there are some examples below that may make learning this valuable function easier than the simple RTFM.

I will be using the world_x dataset for the next example

If we run a simple SELECT JSON_PRETTY(doc) FROM countryinfo LIMIT 1;  the server will return something similar to the following:

{
  "GNP": 828,
  "_id": "ABW",
  "Name": "Aruba",
  "IndepYear": null,
  "geography": {
    "Region": "Caribbean",
    "Continent": "North America",
    "SurfaceArea": 193
  },
  "government": {
    "HeadOfState": "Beatrix",
    "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"
  },
  "demographics": {
    "Population": 103000,
    "LifeExpectancy": 78.4000015258789
  }
}


We can use JSON_TABLE to extract the Name, the Head of State, and the Governmental Form easily with the following query. If you are not used to the MySQL JSON Data type, the "$" references the entire document in the doc column (and doc is out JSON data type column in the table).  And notice that the $.government.HeadOfState and $.government.GovernmentForm are the full path to the keys in the document.

select jt.* FROM countryinfo,
json_table(doc, "$" COLUMNS (
          name varchar(20) PATH "$.Name",
          hofstate varchar(20) PATH '$.government.HeadOfState',
          gform varchar(50) PATH '$.government.GovernmentForm')) as jt
limit 1;

The syntax is JSON_TABLE(expr, path COLUMNS (column_list) [AS] alias) where expr is either a column column from a table or a JSON document passed to the function ('{"Name": "Dave"}' as an example).  Then the desired columns are specified where we name the new column, give it a relational type, and then specify the path of the JSON values we want to cast.

And the results are in the form of a relational table.

+-------+----------+----------------------------------------------+
| name  | hofstate | gform                                        |
+-------+----------+----------------------------------------------+
| Aruba | Beatrix  | Nonmetropolitan Territory of The Netherlands |
+-------+----------+----------------------------------------------+

This is JSON_TABLE in its most basic form.  The only thing I would like to emphasize is that the keys of the JSON data are case sensitive and it is import to check your spelling!

Data Problems

There is also a nice feature to JSON_TABLE where you assign a default value if that key/value pair is missing or yet another value if there is something can not be cast. If we use a non-existent key/value pair here named 'xyz' for an example, we can insert the value '888' for any JSON document missing values.

select jt.* FROM countryinfo,
json_table(doc, "$" COLUMNS (
          name varchar(20) PATH "$.Name",
          hofstate varchar(20) PATH '$.government.HeadOfState',
          xyz int(4) PATH '$.xyz' DEFAULT '999' ON ERROR DEFAULT '888' ON EMPTY,
          gform varchar(50) PATH '$.government.GovernmentForm')) as jt
limit 1;

And how the result looks:

+-------+----------+-----+----------------------------------------------+
| name  | hofstate | xyz | gform                                        |
+-------+----------+-----+----------------------------------------------+
| Aruba | Beatrix  | 888 | Nonmetropolitan Territory of The Netherlands |
+-------+----------+-----+----------------------------------------------+

NULL Handling

Now be careful with Null values. If you change the new line to  xyz int(4) PATH '$.IndepYear' DEFAULT '999' ON ERROR DEFAULT '888' ON EMPTY, we can easily see that the NULL value for Aruba's year of independence will return the default '999' value.  And if you change the path to '$.Name' to try and force the string value into the integer it will trake the ON ERROR path.

And you can assign missing values to NULL 

Nested Path Data

Iterating nested arrays can be painful but JSON_TABLE makes it very simple.  So creating some dummy data, we can start work on digging through the nested information.

select * from a;
+----+-----------------------+
| id | x                     |
+----+-----------------------+
|  1 | {"a": 1, "b": [1, 2]} |
|  2 | {"a": 2, "b": [3, 4]} |
|  3 | {"a": 3, "b": [5, 6]} |

+----+-----------------------+

The query features the NESTED PATH argument

select d.* FROM a, 
JSON_TABLE(x, "$" columns 
        (mya varchar(50) PATH "$.a", 
NESTED PATH "$.b[*]" 
                columns (myb int path '$'))
) as d;

The output.

+-----+-----+
| mya | myb |
+-----+-----+
| 1   |   1 |
| 1   |   2 |
| 2   |   3 |
| 2   |   4 |
| 3   |   5 |
| 3   |   6 |
+-----+-----+
6 rows in set (0.0013 sec)

Not bad but lets add another level.

select * from b;
+----+-----------------------------------------------------+
| id | x                                                   |
+----+-----------------------------------------------------+
|  1 | {"a": 2, "b": [{"c": 101, "d": [44, 55, 66]}]}      |
|  2 | {"a": 1, "b": [{"c": 100, "d": [11, 22, 33]}]}      |
|  3 | {"a": 3, "b": [{"c": 102, "d": [77, 88, 99, 101]}]} |
+----+-----------------------------------------------------+
3 rows in set (0.0009 sec)

So lets embed another level

select d.* FROM b,  
       JSON_TABLE(x, "$" columns          
        (mya varchar(50) PATH "$.a",  
          NESTED PATH "$.b[*]"          
           columns (myc int path '$.c', 
           NESTED PATH '$.d[*]'  
           columns (dpath int path '$'))) 
) as d order by myc;
+-----+-----+-------+
| mya | myc | dpath |
+-----+-----+-------+
| 1   | 100 |    22 |
| 1   | 100 |    33 |
| 1   | 100 |    11 |
| 2   | 101 |    44 |
| 2   | 101 |    55 |
| 2   | 101 |    66 |
| 3   | 102 |    77 |
| 3   | 102 |    88 |
| 3   | 102 |    99 |
| 3   | 102 |   101 |
+-----+-----+-------+
10 rows in set (0.0006 sec)

And we can get ordinal numbers too.

select d.* FROM b,  
         JSON_TABLE(x, "$" columns          
        (mya varchar(50) PATH "$.a",  
           NESTED PATH "$.b[*]"          
           columns (myc int path '$.c', 
           nested path '$.d[*]'  
           columns (dcount for ordinality, 
           dpath int path '$'))) ) as d 
order by dpath;
+-----+-----+--------+-------+
| mya | myc | dcount | dpath |
+-----+-----+--------+-------+
| 1   | 100 |      1 |    11 |
| 1   | 100 |      2 |    22 |
| 1   | 100 |      3 |    33 |
| 2   | 101 |      1 |    44 |
| 2   | 101 |      2 |    55 |
| 2   | 101 |      3 |    66 |
| 3   | 102 |      1 |    77 |
| 3   | 102 |      2 |    88 |
| 3   | 102 |      3 |    99 |
| 3   | 102 |      4 |   101 |
+-----+-----+--------+-------+
10 rows in set (0.0009 sec)

And not that we have the data structured, we can start using the WHERE clause such as  where myc > 100 and dpath < 100.

Advanced MySQL JSON_TABLE

JSON_TABLE is one of the more complex functions that arrived in MySQL 8.  It takes schemaless JSON data and turns it into a relational table. So your NoSQL data becomes SQL data and you can use good ol' SQL where clauses on that temporary relational table! 

I stated writing about JSON_TABLE here and here last December.  And you can find details on using JSON_TABLE in my book.  The following examples cover what to do when key/value pairs are missing or bad, traversing nested paths, and adding an ordinal number to nested values.  These operations provide a great deal of muscle when wrestling NoSQL data into a relational format.

JSON_TABLE

The first argument to JSON_TABLE is the name of the JSON column in the table.  In the following example '$" denotes the entire JSON data type column or JSON document. 

COLUMNS is used to call out the various key/values and assign them to a temporary 'column' and a definition of that column.  In this example the JSON key Population is being called out as an integer to be named Pop.  Oh, if the values are missing for Population, JSON_TABLE will insert the DEFAULT value, here 999, to the results. Or if there is an error in the data, like a string instead of something integer-ish, it output a NULL, a valid JSON string, or the DEFAULT value, which is used in this case.  And if the field is empty (NULL), a valid JSON string, or the DEFAULT value, as you direct -- '987' in the example that follows.

Example 1 -Handling bad or missing data

This example has four records where two of the records need some direction.  The has a bad value ('fish') and an empty value (no Population key in the JSON column). 

mysql> SELECT name, 
          Info->>"$.Population", 
          Pop FROM city2,   
          JSON_TABLE(Info,"$" COLUMNS 
          ( Pop INT PATH "$.Population" 
          DEFAULT '999' 
          ON ERROR DEFAULT 
          '987' ON EMPTY))  
         AS x1;
+-------+-----------------------+------+
| name  | Info->>"$.Population" | Pop  |
+-------+-----------------------+------+
| alpha | 100                   |  100 |
| beta  | fish                  |  999 |
| delta | 15                    |   15 |
| gamma | NULL                  |  987 |
+-------+-----------------------+------+
4 rows in set, 1 warning (0.00 sec)

So 'fish' becomes '999' and NULL becomes '987'.

Example 2

Nested values in JSON data are very common and there is a NESTED PATH operator to allow extracting those values.  The restaurants data set from the MongoDB world has some ratings scores in an array named 'grades' that are nested.

{"_id": "00005b2176ae0000000000000001",
"name": "Morris Park Bake Shop",
"grades": [
{"date": {"$date": 1393804800000}, "grade": "A", "score": 2}, 
{"date": {"$date": 1378857600000}, "grade": "A", "score": 6}, 
{"date": {"$date": 1358985600000}, "grade": "A", "score": 10}, 
{"date": {"$date": 1322006400000}, "grade": "A", "score": 9}, 
{"date": {"$date": 1299715200000}, "grade": "B", "score": 14}],
"address": {"coord": [-73.856077, 40.848447],
"street": "Morris Park Ave",
"zipcode": "10462", "
"cuisine": "Bakery",
"restaurant_id": "30075445"}

The NESTED PATH operator allows access to each of the grades.

mysql> select aaaa.*  from restaurants,  
             json_table(doc, "$" COLUMNS
             (name char(50) path "$.name",   
              style varchar(50) path "$.cuisine",  
             NESTED PATH '$.grades[*]' COLUMNS 
             (Grading char(10) path "$.grade", 
             Score INT path "$.score"))) 
      as aaaa ;
+--------------------------------+------------+---------+-------+
| name                           | style      | Grading | Score |
+--------------------------------+------------+---------+-------+
| Morris Park Bake Shop          | Bakery     | A       |     2 |
| Morris Park Bake Shop          | Bakery     | A       |     6 |
| Morris Park Bake Shop          | Bakery     | A       |    10 |
| Morris Park Bake Shop          | Bakery     | A       |     9 |
| Morris Park Bake Shop          | Bakery     | B       |    14 |
| Wendy'S                        | Hamburgers | A       |     8 |
| Wendy'S                        | Hamburgers | B       |    23 |
| Wendy'S                        | Hamburgers | A       |    12 |
| Wendy'S                        | Hamburgers | A       |    12 |
| Dj Reynolds Pub And Restaurant | Irish      | A       |     2 |
+--------------------------------+------------+---------+-------+
10 rows in set (0.00 sec)

From here the average scores could be computed with a Windowing Function and a Common Table Expression. A big tip of the hat to LeFred for the following

WITH cte1 AS (SELECT doc->>"$.name" AS 'name',
doc->>"$.cuisine" AS 'cuisine',
        (SELECT AVG(score) FROM 
        JSON_TABLE(doc, "$.grades[*]"
        COLUMNS (score INT PATH "$.score")) as r ) AS avg_score
 FROM restaurants)
 SELECT *, rank() OVER 
  (PARTITION BY cuisine ORDER BY avg_score) AS `rank`
  FROM cte1 
  ORDER by `rank`, avg_score DESC limit 10;

Example 3

Not all the restaurant have the same number of grades. But it is very easy to see the number of grades. We can get ordinal numbers next to each grade with  FOR ORDINALITY

mysql> select aaaa.name, aaaa.ordinal, aaaa.Grading  

       FROM restaurants,  json_table(doc, "$" COLUMNS(

         name char(50) path "$.name",   

         style varchar(50) path "$.cuisine",  

         NESTED PATH '$.grades[*]'    COLUMNS (

           ordinal FOR ORDINALITY, 

           Grading char(10) path "$.grade", 

           Score INT path "$.score"))) 

        as aaaa  limit 10;

+--------------------------------+---------+---------+

| name                           | ordinal | Grading |

+--------------------------------+---------+---------+

| Morris Park Bake Shop          |       1 | A       |

| Morris Park Bake Shop          |       2 | A       |

| Morris Park Bake Shop          |       3 | A       |

| Morris Park Bake Shop          |       4 | A       |

| Morris Park Bake Shop          |       5 | B       |

| Wendy'S                        |       1 | A       |

| Wendy'S                        |       2 | B       |

| Wendy'S                        |       3 | A       |

| Wendy'S                        |       4 | A       |

| Dj Reynolds Pub And Restaurant |       1 | A       |

+--------------------------------+---------+---------+

10 rows in set (0.00 sec)

Conclusion

JSON_TABLE provides a way to structure and manipulate unstructured, NoSQL data.  It is a powerful way to present data at the database level that might hamper an application.  Plus it provides a best of both the SQL and NoSQL worlds that leverages the best of 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.