How to Convert a JSON Array into Rows in MySQL?

Convert JSON Array in MySQL to Rows

Converting a JSON array into individual rows in a MySQL table can be a challenge. However, this operation can be achieved using various techniques, including the following:

Method 1: JSON_EXTRACT and Union

This method involves extracting individual array elements using the JSON_EXTRACT function and combining them using the UNION operator. Here's an example:

SET @j = '[1, 2, 3]';

SELECT JSON_EXTRACT(@j, '$[0]') AS value
UNION
SELECT JSON_EXTRACT(@j, '$[1]') AS value
UNION
SELECT JSON_EXTRACT(@j, '$[2]') AS value;

Method 2: JSON_TABLE (MySQL 8 )

MySQL 8 introduces the JSON_TABLE function, which allows you to create a virtual table based on a JSON document. This provides a straightforward way to convert JSON arrays to rows. Here's an example:

SET @j = '[5, 6, 7]';

SELECT *
FROM JSON_TABLE(
  @j,
  "$[*]"
  COLUMNS(
    Value INT PATH "$"
  )
) data;

Method 3: String Splitting and JSON_TABLE

This technique leverages the fact that MySQL lacks a built-in string splitting function. By converting the delimited string into a JSON string, you can then use JSON_TABLE to achieve the split functionality.

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

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

These methods provide versatile solutions for converting JSON arrays into individual rows in MySQL. While MySQL 5.7 may require more complex approaches, MySQL 8 introduces more efficient options like JSON_TABLE, opening up new possibilities for data manipulation.

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