Réécrivez le titre comme suit : Convertir le tableau JSON en lignes dans MySQL

Question

Mise à jour : Ceci est désormais possible dans MySQL 8 via la fonction JSON_TABLE : https://dev.mysql.com/doc/refman/8.0/en/json-table-functions .html≪

J'aime les nouvelles fonctions JSON dans MySQL 5.7, mais j'ai du mal à essayer de fusionner les valeurs de JSON dans une structure de table normale.

Il est facile d'obtenir du JSON, de le manipuler, d'en extraire des tableaux, et bien plus encore. JSON_EXTRACT jusqu'au bout. Mais qu’en est-il de l’inverse, du tableau JSON à la ligne ? Peut-être que je suis intéressé par la fonctionnalité MySQL JSON existante, mais je n'ai pas réussi à le comprendre.

Par exemple, supposons que j'ai un tableau JSON et que je souhaite insérer une ligne pour chaque élément du tableau et sa valeur ? Le seul moyen que j'ai trouvé est d'écrire un tas de JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') etc et de les réunir.

Alternativement, supposons que j'ai un tableau JSON et que je souhaite le GROUP_CONCAT() en une seule chaîne séparée par des virgules ?

En d’autres termes, je sais que je peux faire ceci :

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
  DEPUIS
  (
    SÉLECTIONNER 0 COMME n
    SYNDICAT
    SÉLECTIONNER 1 COMME n
    SYNDICAT
    SÉLECTIONNER 2 COMME n
    SYNDICAT
    SÉLECTIONNER 3 COMME n
    SYNDICAT
    SÉLECTIONNER 4 COMME n
    SYNDICAT
    SÉLECTIONNER 5 COMME n
  ) X
OÙ x.n <JSON_LENGTH (@j);

Mais ça me fait mal aux yeux. Et mon cœur.

Comment puis-je faire quelque chose comme ceci :

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT (@j, '$[ * ]'))

...et concaténer les valeurs du tableau avec le tableau JSON lui-même ?

Je pense que ce que je recherche ici, c'est une sorte de JSON_SPLIT , quelque chose comme :

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(val)
DEPUIS
  JSON_SPLIT(JSON_EXTRACT (@j, '$[ * ]'), '$')

Si MySQL avait une fonction de retour de table STRING_SPLIT(val, 'separator') appropriée, je pourrais la pirater (cette foutue évasion), mais cela ne fonctionne pas non plus.

Answer 1

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.)

Answer 2

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;