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>