How Can I Efficiently Convert JSON Arrays into Rows in MySQL?

Converting JSON Arrays to Rows in MySQL

In MySQL 5.7, JSON manipulation has been greatly enhanced, providing diverse functionality for extracting and processing data within JSON structures. However, the inverse operation of converting a JSON array into rows has not been straightforward.

JSON Array to Rows

To insert a row for each element in a JSON array, a manual approach using multiple JSON_EXTRACT() queries was previously necessary. But with the introduction of the JSON_TABLE function in MySQL 8, this process has become more streamlined:

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

JSON Array to Comma-separated String

To convert a JSON array into a comma-separated string using GROUP_CONCAT(), a trick can be employed:

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

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

This approach treats the delimited string as a JSON string, resulting in a converted comma-separated string.

Missing STRING_SPLIT Function

While MySQL lacks a dedicated STRING_SPLIT() function, the JSON_TABLE function can serve as a substitute for split operations, effectively breaking down a delimited string into its components.

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