Il est vrai que la dénormalisation en JSON n'est pas une bonne idée, mais parfois vous devez gérer des données JSON, et il existe un moyen d'extraire un tableau JSON en lignes dans une requête.

L'astuce consiste à effectuer une jointure sur une table d'index temporaire ou en ligne, ce qui vous donne une ligne pour chaque valeur non nulle du tableau JSON. Autrement dit, si vous avez une table avec les valeurs 0, 1 et 2 et que vous la joignez à un tableau JSON "fish" qui contient deux entrées, alors Fish[0] correspond à 0, produisant ainsi une ligne, et Fish 1 correspond à 1, aboutit à la deuxième ligne, mais fish[2] est nul, donc il ne correspond pas à 2 et aucune ligne n'est produite dans la jointure. Vous avez besoin d'autant de nombres dans la table d'index que de longueur maximale de n'importe quel tableau dans les données JSON. C'est un peu un hack, et aussi douloureux que l'exemple du PO, mais c'est très pratique.

Exemple (nécessite MySQL 5.7.8 ou supérieur) :

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;

Le résultat est :

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

On dirait que l'équipe MySQL pourrait ajouter une fonction JSON_TABLE dans MySQL 8 pour rendre cela plus facile. (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/JSON_TABLE 函数，以使这一切变得更容易。 (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/) （MySQL 团队已经添加了JSON_TABLE) (L'équipe MySQL a ajouté JSON_TABLE fonction.)

Voici comment utiliser JSON_TABLE执行此操作> dans MySQL 8+ :

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

Vous pouvez également l'utiliser comme fonction de fractionnement de chaîne à usage général qui manque à MySQL (similaire à regexp_split_to_table de PG ou STRING_SPLIT de MSSQL) en prenant la chaîne délimitée et en la convertissant en chaîne JSON :

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

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