JSON Array to ROWS – MySQL

I recently received a request to divide a JSON data type row value containing an array of numbers into multiple rows. That is one value in each row.

I thought I’d share two of the answers I found with you because they both worked like a charm.

The following format is used to store the column value.
{“id”: 1, “values”: [3, 4, 12, 5, 7, 9]}

Here is the complete code along with a sample table and data.

CREATE TABLE `JSONTable` (
`id` int ,
`context` json
) ;

INSERT INTO JSONTable VALUES (1,'{“id”: 1, “values”: [
3,
4,
12,
5,
7,
9
]}’);

SOLUTION-1:

SELECT json_extract(context,”$.values[*]”)
This returns:
[3, 4, 12, 5, 7, 9]

To remove the brackets, I used the REPLACE function.

SELECT REPLACE(REPLACE(json_extract(context,”$.values[*]”),'[‘,”),’]’,”) from JSONTable;
Now, this returned:
3, 4, 12, 5, 7, 9

Thus, the following is the first solution to this problem.

CREATE TEMPORARY TABLE temp (val CHAR(255));

SET @S1 = CONCAT(“INSERT INTO temp (val) VALUES (‘”,REPLACE((SELECT GROUP_CONCAT( DISTINCT
REPLACE(REPLACE(json_extract(context,”$.values[*]”),'[‘,”),’]’,”)
) AS data FROM `JSONTable`), “,”, “‘),(‘”),”‘);”);

PREPARE stmt1 FROM @s1;

EXECUTE stmt1;

SELECT DISTINCT(val) FROM temp;

Solution:2

SELECT
SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(json_extract(context,”$.values[*]”),'[‘,”),’]’,”), ‘,’, numbers.n), ‘,’, -1) name
from
(SELECT 1 n UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8) numbers INNER JOIN JSONTable
on CHAR_LENGTH(REPLACE(REPLACE(json_extract(context,”$.values[*]”),'[‘,”),’]’,”))
-CHAR_LENGTH(REPLACE(REPLACE(REPLACE(json_extract(context,”$.values[*]”),'[‘,”),’]’,”), ‘,’, ”))>=numbers.n-1;

Considering that the second approach is not dynamic, I do not favor it or suggest it. The numbers specified in the “numbers” table must be increased if the number of elements in an array increases.

However, the following is recommended.

WITH recursive
T as ( SELECT(SELECT REPLACE(REPLACE(json_extract(context,”$.values[*]”),'[‘,”),’]’,”) FROM JSONTable) AS items),
N AS ( SELECT 1 AS n UNION SELECT n + 1 FROM N, T
WHERE n <= LENGTH(items) – LENGTH(REPLACE(items, ‘,’, ”)))
SELECT DISTINCT SUBSTRING_INDEX(SUBSTRING_INDEX(items, ‘,’, n), ‘,’, -1)
group_name FROM N, T;

Hope you find this post helpful.