MySQL JSON_TABLE() Function

Summary: in this tutorial, you will learn how to use the MySQL JSON_TABLE() function to convert JSON data to tabular data.

Introduction to MySQL JSON_TABLE() function

The JSON_TABLE() function allows you to extract data from JSON documents and convert it into tabular data.

The following shows the syntax of the JSON_TABLE() function:

JSON_TABLE(json_doc, path COLUMNS (column_list) [AS] alias);Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_doc: This is the JSON document that you want to extract data from.
• path: The JSON path expression that specifies the location of the data in the json_doc document to be extracted.
• COLUMNS(column_list): The COLUMNS clause specifies a list of columns of the resulting table.
• AS alias: The table alias is required but the AS keyword is optional.

MySQL JSON_TABLE function example

Suppose you have the following JSON document:

{ "name" : "John", 
"age" : 22}Code language: SQL (Structured Query Language) (sql)

The JSON document is an object that has two keys name and age. To convert the JSON document to tabular data, you can use the JSON_TABLE() function.

First, pass the JSON document to the first argument of the function:

JSON_TABLE('{"name":"John","age":22}',...)Code language: SQL (Structured Query Language) (sql)

Second, specify the path to which you want to extract data. Since we’re going to convert the whole document, we can use the '$' path expression:

JSON_TABLE('{"name":"John","age":22}', '$' )Code language: SQL (Structured Query Language) (sql)

Third, specify a list of columns for the resulting table:

JSON_TABLE(
  '{"name":"John","age":22}', 
  '$' COLUMNS(...)
)Code language: SQL (Structured Query Language) (sql)

Suppose that the resulting table has two columns name and age that will have the value of the name and age properties of the JSON object.

To do so, you list the columns in the COLUMNS clause. For each column, you specify the column name, data type, and JSON path expression that you want to extract data from the JSON document and populate it to the column:

JSON_TABLE(
  '{"name":"John","age":22}', 
  '$' COLUMNS(
    name VARCHAR(255) PATH '$.name', 
    age INT PATH '$.age'
  )
)Code language: SQL (Structured Query Language) (sql)

Fourth, specify a table alias:

JSON_TABLE(
  '{"name":"John","age":22}', 
  '$' COLUMNS(
    name VARCHAR(255) PATH '$.name', 
    age INT PATH '$.age'
  )
) person;Code language: SQL (Structured Query Language) (sql)

Finally, select all columns from the result set of the JSON_TABLE() function:

SELECT 
  * 
FROM 
  JSON_TABLE(
    '{"name":"John","age":22}', 
    '$' COLUMNS(
      name VARCHAR(255) PATH '$.name', 
      age INT PATH '$.age'
    )
  ) as person;Code language: SQL (Structured Query Language) (sql)

Output:

+------+------+
| name | age  |
+------+------+
| John |   22 |
+------+------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the JSON_TABLE function to convert JSON documents into tabular data.