How Can I Convert JSON Arrays to Rows and Use GROUP_CONCAT() in MySQL?

JSON Array to Rows Conversion in MySQL

Introduction

Working with JSON arrays in MySQL requires an understanding of its JSON manipulation functions. However, a challenge arises when converting these arrays into rows or performing operations like GROUP_CONCAT() on them. This article addresses such scenarios and provides solutions using MySQL's JSON functionality.

From JSON Array to Rows

To insert a row for each element in a JSON array, one approach is to manually extract each element using JSON_EXTRACT() and union the results together. However, a cleaner method is to utilize MySQL 8's JSON_TABLE function:

SET @j = '[1, 2, 3]';
SELECT *
     FROM
       JSON_TABLE(
         @j,
         "$[*]"
         COLUMNS(
           Value INT PATH "$"
         )
       ) data;

This will create a table with one column, "Value," containing the elements of the JSON array.

GROUP_CONCAT() on JSON Array

To concatenate the elements of a JSON array into a comma-separated string using GROUP_CONCAT(), you can use a combination of JSON_TABLE and GROUP_CONCAT():

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

JSON Splitting

For scenarios similar to STRING_SPLIT(), such as converting a comma-separated string into rows, you can leverage JSON_TABLE by converting the string into a JSON array:

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

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

This will create a table with one column, "Value," containing the elements of the delimited string.

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