How to Use JSON_TABLE in MySQL: Syntax and Examples

In MySQL, JSON_TABLE converts JSON data into a relational table format by defining columns and paths inside the JSON. You use it in the FROM clause to extract JSON array elements as rows with specified columns.

Syntax

The JSON_TABLE function takes a JSON document and a path expression to create a virtual table. You define columns with names, data types, and JSON paths inside the JSON array or object.

• json_doc: The JSON data source.
• path: JSON path to the array to convert into rows.
• COLUMNS clause: Defines columns with names, types, and JSON paths for each row.

sql

JSON_TABLE(
  json_doc,
  path COLUMNS (
    column_name data_type PATH 'json_path',
    ...
  )
)

Example

This example shows how to extract data from a JSON array of objects into rows and columns using JSON_TABLE. It converts each JSON object into a row with columns for id and name.

sql

SELECT *
FROM JSON_TABLE(
  '[{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]',
  '$[*]' COLUMNS (
    id INT PATH '$.id',
    name VARCHAR(50) PATH '$.name'
  )
) AS jt;

Output

id | name
---|-------
1  | Alice
2  | Bob

id	name
1	Alice
2	Bob

Common Pitfalls

Common mistakes when using JSON_TABLE include:

• Using incorrect JSON paths that do not match the JSON structure.
• Not specifying the correct data type for columns, causing conversion errors.
• Forgetting to alias the JSON_TABLE result, which is required in the FROM clause.

Always verify your JSON paths and data types match the JSON data.

sql

/* Wrong: Missing alias for JSON_TABLE */
SELECT *
FROM JSON_TABLE(
  '[{"id":1}]',
  '$[*]' COLUMNS (id INT PATH '$.id')
);

/* Right: Add alias */
SELECT *
FROM JSON_TABLE(
  '[{"id":1}]',
  '$[*]' COLUMNS (id INT PATH '$.id')
) AS jt;

Quick Reference

Part	Description
json_doc	JSON string or column containing JSON data
path	JSON path to the array to convert into rows
COLUMNS	Defines columns with name, type, and JSON path
column_name	Name of the output column
data_type	SQL data type for the column (e.g., INT, VARCHAR)
PATH 'json_path'	JSON path expression to extract data for the column

Key Takeaways

Use JSON_TABLE to convert JSON arrays into relational rows and columns in MySQL.

Define columns with names, data types, and JSON paths inside the COLUMNS clause.

Always alias the JSON_TABLE result in the FROM clause.

Check JSON paths carefully to match your JSON structure.

Specify correct data types to avoid conversion errors.