MySQL JSON_TABLE Function

The MySQL JSON_TABLE() function converts part of a JSON document into a relational result set. You define a row path, describe the output columns, and then query the generated rows with normal SQL.

This function is useful when an application stores arrays or nested objects in a JSON column but a report, filter, or join needs typed SQL columns. MySQL supports JSON_TABLE() in the MySQL 8.x series.

JSON_TABLE Syntax

JSON_TABLE() accepts a JSON expression, a path that produces rows, a COLUMNS definition, and a required table alias.

Syntax:

JSON_TABLE(
  json_expression,
  row_path COLUMNS (
    column_definitions
  )
) AS table_alias

Convert a JSON Array into Rows

The path $[*] selects every item in a top-level array. Each column path then reads a property from the current item.

Example:

SET @orders = '[
  {"id": 101, "customer": "Aarav", "total": 850.50},
  {"id": 102, "customer": "Emma", "total": 1240.00}
]';

-- Convert each array item into one relational row.
SELECT jt.order_id, jt.customer_name, jt.total
FROM JSON_TABLE(
  @orders,
  '$[*]' COLUMNS (
    order_id INT PATH '$.id',
    customer_name VARCHAR(50) PATH '$.customer',
    total DECIMAL(10, 2) PATH '$.total'
  )
) AS jt;

The SQL types in COLUMNS convert extracted JSON scalar values. Choose types that match the expected data and the operations you plan to perform.

Add Row Numbers with FOR ORDINALITY

A FOR ORDINALITY column counts generated rows starting at 1. It is useful when the array position has meaning.

Example:

SELECT jt.position, jt.task_name
FROM JSON_TABLE(
  '["Design", "Review", "Deploy"]',
  '$[*]' COLUMNS (
    position FOR ORDINALITY,
    task_name VARCHAR(40) PATH '$'
  )
) AS jt;

Check Whether a Path Exists

An EXISTS PATH column returns 1 when the path exists and 0 when it does not. This helps distinguish optional properties without extracting their values.

Example:

SELECT jt.product_name, jt.has_discount
FROM JSON_TABLE(
  '[
    {"name": "Keyboard", "discount": 10},
    {"name": "Mouse"}
  ]',
  '$[*]' COLUMNS (
    product_name VARCHAR(50) PATH '$.name',
    has_discount INT EXISTS PATH '$.discount'
  )
) AS jt;

Expand Nested Arrays

Use NESTED PATH to generate rows from an array inside each parent object. Parent columns remain available in every child row.

Example:

SET @departments = '[
  {
    "department": "Engineering",
    "members": [
      {"name": "Olivia", "role": "Developer"},
      {"name": "Rohan", "role": "Tester"}
    ]
  },
  {
    "department": "Sales",
    "members": [
      {"name": "James", "role": "Manager"}
    ]
  }
]';

-- Expand members while retaining the parent department.
SELECT jt.department, jt.member_name, jt.member_role
FROM JSON_TABLE(
  @departments,
  '$[*]' COLUMNS (
    department VARCHAR(40) PATH '$.department',
    NESTED PATH '$.members[*]' COLUMNS (
      member_name VARCHAR(50) PATH '$.name',
      member_role VARCHAR(40) PATH '$.role'
    )
  )
) AS jt;

Handle Missing and Invalid Values

Column definitions may specify behavior for an empty path or a conversion error. Available actions include NULL, DEFAULT, and ERROR.

Example:

SELECT jt.sku, jt.quantity
FROM JSON_TABLE(
  '[
    {"sku": "A-10", "quantity": 4},
    {"sku": "B-20"},
    {"sku": "C-30", "quantity": "unknown"}
  ]',
  '$[*]' COLUMNS (
    sku VARCHAR(20) PATH '$.sku',
    quantity INT PATH '$.quantity'
      DEFAULT '0' ON EMPTY
      NULL ON ERROR
  )
) AS jt;

ON EMPTY applies when the path has no value. ON ERROR applies when extraction or conversion fails. Use strict ERROR behavior when bad data must stop the query rather than become a default.

Use JSON_TABLE with a Table Column

You can reference a JSON column from the table that appears before JSON_TABLE() in the FROM clause. The generated rows are correlated with each source row.

Example:

CREATE TABLE surveys (
  survey_id INT PRIMARY KEY,
  answers JSON NOT NULL
);

INSERT INTO surveys VALUES
(1, '[{"question":"speed","score":5},{"question":"support","score":4}]'),
(2, '[{"question":"speed","score":3}]');

-- Create one row per stored answer.
SELECT s.survey_id, a.question, a.score
FROM surveys AS s
JOIN JSON_TABLE(
  s.answers,
  '$[*]' COLUMNS (
    question VARCHAR(30) PATH '$.question',
    score INT PATH '$.score'
  )
) AS a
ORDER BY s.survey_id, a.question;

Filter and Aggregate Generated Rows

Because JSON_TABLE() returns a relational table, normal WHERE, GROUP BY, joins, and aggregate functions work on its columns.

Example:

-- Calculate the average score for every survey question.
SELECT a.question, AVG(a.score) AS average_score
FROM surveys AS s
JOIN JSON_TABLE(
  s.answers,
  '$[*]' COLUMNS (
    question VARCHAR(30) PATH '$.question',
    score INT PATH '$.score'
  )
) AS a
GROUP BY a.question;

Practical Guidelines

• Validate and normalize incoming JSON before relying on a fixed structure.
• Select only the paths and columns the query needs.
• Choose explicit SQL types and suitable sizes for extracted values.
• Decide whether missing or invalid data should produce NULL, a default, or an error.
• Use ordinary relational columns and indexes for values that you filter or join frequently.
• Check execution plans when expanding large documents because every generated row adds work.

Tip: Use JSON_TABLE() when JSON is the source format but SQL needs rows. It does not automatically make a heavily queried JSON structure faster than a well-designed relational schema.

Conclusion

MySQL JSON_TABLE() bridges JSON documents and relational SQL. Define the row path, map typed columns, use ordinality or existence checks when needed, and apply NESTED PATH for child arrays. Once generated, the rows can be filtered, joined, sorted, and aggregated like any other table.