What is JSON Table in MySQL 8: Explanation and Example

In MySQL 8, JSON_TABLE is a function that converts JSON data into a relational table format. It lets you extract and query JSON arrays or objects as if they were normal database tables, making JSON data easier to work with using SQL.

How It Works

Imagine you have a box full of different toys (JSON data), but you want to organize them on shelves (table rows) so you can easily find and use each toy. JSON_TABLE acts like a helper that takes the toys out of the box and arranges them neatly on shelves based on your instructions.

Technically, JSON_TABLE reads JSON arrays or objects stored in a column and creates a virtual table with rows and columns. You define how to map JSON keys or array elements to table columns. This way, you can use regular SQL queries to filter, join, or aggregate the JSON data as if it were stored in normal tables.

This function is very useful because JSON is flexible but hard to query directly. JSON_TABLE bridges the gap by turning JSON into structured rows and columns on the fly.

Example

This example shows how to use JSON_TABLE to extract data from a JSON array stored in a column and turn it into rows and columns.

sql

CREATE TABLE orders (
  id INT PRIMARY KEY,
  order_data JSON
);

INSERT INTO orders VALUES
(1, '[{"item": "apple", "qty": 10}, {"item": "banana", "qty": 5}]');

SELECT o.id, jt.item, jt.qty
FROM orders o,
JSON_TABLE(o.order_data, '$[*]'
  COLUMNS (
    item VARCHAR(20) PATH '$.item',
    qty INT PATH '$.qty'
  )
) AS jt;

Output

id | item   | qty
---|--------|----
1  | apple  | 10
1  | banana | 5

When to Use

Use JSON_TABLE when you store JSON data in MySQL but want to query it like normal tables. It is great for:

• Extracting details from JSON arrays or objects for reporting or filtering.
• Joining JSON data with other tables.
• Transforming flexible JSON into structured formats for easier analysis.

For example, if you have an orders table with JSON details about items, JSON_TABLE helps you list each item as a separate row to calculate totals or find popular products.

Key Points

• JSON_TABLE converts JSON data into relational rows and columns.
• You define the mapping of JSON keys to table columns using COLUMNS and PATH.
• It works on JSON arrays or objects stored in columns.
• Enables powerful SQL queries on JSON data without complex JSON functions.
• Available in MySQL 8 and later versions.

Key Takeaways

JSON_TABLE turns JSON data into a table format for easy SQL querying.

It maps JSON keys or array elements to columns and rows dynamically.

Use it to extract, filter, and join JSON data like normal tables.

This feature is available starting in MySQL 8.

It simplifies working with JSON by bridging flexible data and structured queries.