Convert JSON array in MySQL to rows

It's true that it's not a good idea to denormalize into JSON, but sometimes you need to deal with JSON data, and there's a way to extract a JSON array into rows in a query.

The trick is to perform a join on a temporary or inline table of indexes, which gives you a row for each non-null value in a JSON array. I.e., if you have a table with values 0, 1, and 2 that you join to a JSON array “fish” with two entries, then fish[0] matches 0, resulting in one row, and fish1 matches 1, resulting in a second row, but fish[2] is null so it doesn't match the 2 and doesn't produce a row in the join. You need as many numbers in the index table as the max length of any array in your JSON data. It's a bit of a hack, and it's about as painful as the OP's example, but it's very handy.

Example (requires MySQL 5.7.8 or later):

CREATE TABLE t1 (rec_num INT, jdoc JSON);INSERT INTO t1 VALUES   (1, '{"fish": ["red", "blue"]}'),   (2, '{"fish": ["one", "two", "three"]}');SELECT  rec_num,  idx,  JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) AS fishesFROM t1  -- Inline table of sequential values to index into JSON arrayJOIN (   SELECT  0 AS idx UNION  SELECT  1 AS idx UNION  SELECT  2 AS idx UNION  -- ... continue as needed to max length of JSON array  SELECT  3  ) AS indexesWHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULLORDER BY rec_num, idx;

The result is:

+---------+-----+---------+| rec_num | idx | fishes  |+---------+-----+---------+|       1 |   0 | "red"   ||       1 |   1 | "blue"  ||       2 |   0 | "one"   ||       2 |   1 | "two"   ||       2 |   2 | "three" |+---------+-----+---------+

It looks like the MySQL team may add a JSON_TABLE function in MySQL 8 to make all this easier. (http://mysqlserverteam.com/mysql-8-0-labs-json-aggregation-functions/) (The MySQL team has added a JSON_TABLE function.)

Here's how to do this with JSON_TABLE in MySQL 8+:

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

You can also use this as a general string split function which MySQL otherwise lacks (similar to PG's regexp_split_to_table or MSSQL's STRING_SPLIT) by taking a delimited string and turning it into a JSON string:

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

In 2018. What I do for this case.

1. Prepare a table with just continually number in rows.

   CREATE TABLE `t_list_row` (`_row` int(10) unsigned NOT NULL,PRIMARY KEY (`_row`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;INSERT t_list_row VALUES (0), (1), (2) .... (65535) big enough;

2. Enjoy easy JSON array to rows in the future.

   SET @j = '[1, 2, 3]';SELECT JSON_EXTRACT(@j, CONCAT('$[', B._row, ']'))FROM (SELECT @j AS B) AS AINNER JOIN t_list_row AS B ON B._row < JSON_LENGTH(@j);

For this way. is some kind like 'Chris Hynes' way. but you don't need to know array size.

Good: Clear, short, easy code, no need to know array size, no loop, no invoke other function will be fast.

Bad: You need one more table with enough rows.