How Can I Efficiently Extract Rows from JSON Arrays in MySQL?

Extracting Rows from JSON Arrays in MySQL

MySQL 5.7 introduces powerful JSON functions, enabling seamless manipulation of JSON data. However, extracting JSON array elements into rows has proven challenging.

The Problem

Transforming JSON arrays into a row-based structure remains a roadblock in MySQL. Options are limited to manually extracting individual elements using JSON_EXTRACT() or unioning multiple queries. This approach is cumbersome and error-prone.

The Solution: MySQL 8 and JSON_TABLE

MySQL 8 introduces JSON_TABLE, a comprehensive solution for working with JSON data. It allows direct conversion of JSON arrays into table rows.

SELECT *
FROM
JSON_TABLE(
'[5, 6, 7]',
"$[*]"
COLUMNS(
Value INT PATH "$"
)
) data;

Using JSON_TABLE as a Universal Splitter

In addition to row extraction, JSON_TABLE can serve as a versatile string splitter, a feature missing in MySQL. By converting a delimited string into JSON, it becomes possible to easily separate its components.

set @delimited = 'a,b,c';

SELECT *
FROM
JSON_TABLE(
CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
"$[*]"
COLUMNS(
Value varchar(50) PATH "$"
)
) data;

Conclusion

JSON_TABLE empowers MySQL users with a streamlined approach to converting JSON arrays into rows, eliminating the need for complex unions or manual extraction. It also serves as a handy string splitting tool, addressing a notable gap in MySQL's functionality.

The above is the detailed content of How Can I Efficiently Extract Rows from JSON Arrays in MySQL?. For more information, please follow other related articles on the PHP Chinese website!