JSON_TABLE — Shredding JSON into Rows

JSON_TABLE

JSON_TABLE() converts JSON arrays into relational rows, allowing you to join JSON data with regular tables.

Example

SELECT p.name, jt.tag
FROM products p,
JSON_TABLE(
  p.attributes->'$.tags',
  '$[*]' COLUMNS (tag VARCHAR(50) PATH '$')
) AS jt
WHERE p.id = 1;

SQL

Pro Tip

JSON_TABLE is available in MySQL 8.0+ and is very powerful for ETL-like operations.