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 …
JSON has proven to be a very import data format with immense
popularity. A good part of my time for the last two or so years
has been dedicated to this area and I even wrote a book on the subject. This is a
comparison of the implementations of handling JSON data in MySQL
and MariaDB. I had requests from the community and customers for
this evaluation.
JSON Data Types Are Not All Equal
MySQL added a JSON data type in version 5.7 and it has proven to
be very popular. MariaDB has JSON
support version 10.0.16 but is actually an alias to a
longtext data type so that statement based replication
from MySQL to MariaDB is possible.
MySQL stores JSON documents are …
“MySQL’s JSON data type is great! But how do you index the JSON data?” I was recently presenting at the CakePHP Cakefest Conference and was asked that very question. And I had to admit I had not been able to play, er, experiment with the JSON datatype to that level. Now I have and it is fairly easy.
1. Create a simple table
mysql> desc colors;
+--------------+----------+------+-----+---------+-------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+-------------------+
| popular_name | char(10) | YES | | NULL | |
| hue | json | YES | | NULL | |
+--------------+----------+------+-----+---------+-------------------+
2 rows in set (0.00 sec)
2. Add in some data
INSERT INTO `colors` VALUES ('red','{\"value\":
\"f00\"}'),('green','{\"value\": …