2
votes

Comment obtenir des éléments liés par identifiant dans la colonne de tableau d'entiers JSON dans MySQL

Mon objectif est de savoir comment obtenir une relation entre une colonne qui a des références dans un tableau json à une autre. De manière simplifiée, j'ai deux tableaux:

select version();

CREATE TABLE `table_a` (
  `id` int(11) NOT NULL,
  `references` json NULL
);

CREATE TABLE `table_b` (
  `id` int(11) NOT NULL,
  `name` text NULL
);


INSERT INTO `table_a` (`id`, `references`) VALUES
(1, '\"[1,3]\"'),
(2, '\"[2,3]\"');

INSERT INTO `table_b` (`id`, `name`) VALUES
(1, 'item_1'),
(2, 'item_2'),
(3, 'item_3');


SELECT * from table_a;
SELECT * from table_b;

select `references` from table_a where id=1;

SELECT JSON_SEARCH((select `references` from table_a where id=1), 'one', '3');

Dont les références sont un tableau json d'entiers et la table b

|-----|----------|
| 1   | "item 1" |
| 3   | "item 3" |

Donc, je voudrais obtenir tous les éléments de la table B liés à un élément de la table A avec l'identifiant, par exemple, 1 qui ont leurs identifiants dans la colonne références tableau entier (comme json).

Quelque chose comme ça:

table_b
| id  | name     |
|-----|----------|
| 1   | "item 1" |
| 2   | "item 2" |
| 3   | "item 3" |

J'ai essayé d'y parvenir avec json_contains , json_extract , json_search , etc. de la documentation et je pense que ce problème est de faire correspondre les valeurs à l'intérieur du tableau d'entiers json.

Par exemple:

SELECT JSON_SEARCH((select references from table_a where id=1), 'one', '3');

doit retourner quelque chose mais toujours retourner NULL et je ne comprends pas bien. J'ai aussi essayé avec 3 sans guillemets.

Une idée?

Ma version actuelle de MySQL est la 5.7.25

Merci d'avance.

Code minimal à reproduire:

table_a
| id  | references |
|-----|------------|
| 1   |  "[1,3]"   |
| 2   |  "[2,3]"   |

Sandbox à tester: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=ac557666852fa94e77fdf87158c9abe0

json mysql mysql-json

7 commentaires

Afficher les données source sous forme de scripts CREATE TABLE + INSERT INTO (ou créer un violon en ligne). Afficher la sortie souhaitée sous forme de tableau textuel formaté.

Pourquoi utilisez-vous un tableau JSON au lieu d'un schéma normal normalisé?

@Akina je l'ai mis à jour.

@ Fraise je le sais. Mais c'est l'exigence du modèle de données en ce moment. Si je ne peux pas y parvenir, je vais le refactoriser.

Je ne vois pas le résultat souhaité ..

@Akina Merci pour votre aide. J'ai mis à jour à nouveau. Pardon :)

À la fin. J'ai changé de modèle car cela pose beaucoup de problèmes. Je ne me souviens jamais du principe KISS. Merci à tous pour votre aide.

3 Réponses :

1
votes

JSON incorrect (mais vérifié avec succès par la fonction JSON_VALID).

La solution est monstrueuse:

SELECT table_b.*
FROM table_a, table_b
WHERE table_a.id = 1
  AND JSON_SEARCH(REPLACE(REPLACE(REPLACE(REPLACE(table_a.references, '"', ''), '[', '["'), ']', '"]'), ',', '","'), 'one', table_b.id) IS NOT NULL

tripoter quelques requêtes supplémentaires qui expliquent le problème.

0 commentaires

1
votes

Vous pouvez utiliser la requête suivante comme solution:

select
    table_a.*,
    table_b.*
from table_a 
join table_b on JSON_CONTAINS(
    CAST(TRIM('"' FROM `references`) as JSON), 
    CAST(table_b.id as JSON)
)
where table_a.id=2;

Étant donné que votre champ de references n'est pas de type JSON valide, vous devez le convertir en JSON et après cela, les fonctions JSON_CONTAINS peuvent être utilisées.

Essayez-le ici

0 commentaires

1
votes

Vous pouvez d'abord vous débarrasser des guillemets enveloppant le tableau, puis concaténer les entiers résultants avec des sous-chaînes item_ avec l'utilisation de sous-requêtes auxiliaires dont l'une génère des lignes afin d'obtenir consécutivement chaque membre du tableau tel que

SELECT b.*
  FROM
  (
    SELECT  @i := @i + 1 AS rn,
            CONCAT('item_',JSON_EXTRACT(JSON_UNQUOTE(`references`), 
                                        CONCAT('$[',@i-1,']'))) AS name
      FROM information_schema.tables 
     CROSS JOIN `table_a` AS a
     CROSS JOIN (SELECT @i := 0) r
     WHERE @i < JSON_LENGTH(JSON_UNQUOTE(`references`)) ) AS a
  JOIN `table_b` AS b  
    ON b.`name` = a.name

Démo

0 commentaires