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

The trick is to perform a join on a temporary or inline index table, which gives you a row for each non-null value in the JSON array. That is, if you have a table with values ​​0, 1, and 2, and you join it to a JSON array "fish" that contains two entries, then Fish[0] matches 0, resulting in a row, and Fish1 matches 1, resulting in the second row, but fish[2] is null, so it does not match 2 and no row is produced in the join. You need as many numbers in the index table as the maximum length of any array in the JSON data. This is a bit of a hack, and as painful as the OP's example, but it's very convenient.

Example (requires MySQL 5.7.8 or higher):

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 fishes
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  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 indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx;

turn out:

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

Looks like the MySQL team may be adding a JSON_TABLE function in MySQL 8 to make this easier. (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/) (MySQL Teamhas addedJSON_TABLE function.)

Here's how to use JSON_TABLE执行此操作> in MySQL 8:

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

You can also use it as a general-purpose string splitting function that MySQL lacks (similar to PG's regexp_split_to_table or MSSQL's STRING_SPLIT) by taking the delimited string and converting it to a JSON string:

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

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