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.
…[Read more]
