Query JSON data as relational in MySQL

MySQL can support the JSON datatype, and allows to write queries that can query the JSON data directly. In the case where the JSON value is an array of objects, we will see how we can query the table and retrieve the JSON data as relational data. We will see how we can use JSON_TABLE function to achieve this.
Fun fact: JSON_TABLE is MySQL’s first table function; the return value is not a scalar value, but a result set.

So, let’s consider the following table:

CREATE TABLE `json_data` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `json_column` JSON NULL,
  PRIMARY KEY (`id`));

We will insert a row with a JSON array

INSERT INTO `json_data` (`json_column`) 
VALUES ('[{\"firstName\":\"Mike\",\"lastName\":\"Wazowski\"},{\"firstName\":\"James\",\"lastName\":\"Salivan\"},{\"firstName\":\"Henry\",\"lastName\":\"Waternoose\"}]');

Note: Here is a better view of the JSON we will use in our examples

[
    { "firstName": "Mike", "lastName": "Wazowski" }, 
    { "firstName": "James", "lastName": "Salivan" }, 
    { "firstName": "Henry", "lastName": "Waternoose" }
]

Using the JSON_TABLE function we can return the data within the JSON array in a relational form.

SELECT jd.*
FROM json_data,
   JSON_TABLE(
     json_column,
     "$[*]"
     COLUMNS(
       name JSON PATH "$.firstName",
       surname JSON PATH "$.lastName"
     )
   ) AS jd

The query above will return the following dataset,and you have the JSON fields as db columns.

|-------------|-------------|
|   name      |  surname    |
|-------------|-------------|
|  Mike       |  Wazowski   |
|  James      |  Salivan    |
|  Henry      |  Waternoose |
|-------------|-------------|

You can also add columns to the table and have in the SELECT clause columns both from the table and the JSON data.

CREATE TABLE `row_json_data` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `company_name` VARCHAR(45) NULL,
  `city` VARCHAR(45) NULL,
  `stuff` JSON NULL,
  PRIMARY KEY (`id`));

For example, in the table above, we add the following data.

INSERT INTO `row_json_data` (`company_name`, `city`, `stuff`) 
VALUES ('Monsters Inc', 'Monstropolis', '[{\"lastName\": \"Wazowski\", \"firstName\": \"Mike\"}, {\"lastName\": \"Salivan\", \"firstName\": \"James\"}, {\"lastName\": \"Waternoose\", \"firstName\": \"Henry\"}]');

We can use both columns from the table and the ones defined in the JSON_TABLE function.

SELECT company_name, city, name, surname
FROM row_json_data,
	 JSON_TABLE(
	   staff,
	   "$[*]"
	   COLUMNS(
	     name JSON PATH "$.firstName",
	     surname JSON PATH "$.lastName"
	   )
	 ) AS jd

For that query, the result data will be similar if the data from the JSON_TABLE where joined with the other relational data.

|---------------|---------------|---------------|---------------|
|  company_name |  city         |   name        |  surname      |
|---------------|---------------|---------------|---------------|
|  Monsters Inc |  Monstropolis |  Mike         |  Wazowski     |
|  Monsters Inc |  Monstropolis |  James        |  Salivan      |
|  Monsters Inc |  Monstropolis |  Henry        |  Waternoose   |
|---------------|---------------|---------------|---------------|

Note: The above query will work not only for JSON columns, but also with VARCHAR and TEXT columns.