Comment rejoindre des tables MySQL en fonction des identifiants stockés dans un champ JSON ?

Utiliser le champ JSON MySQL pour joindre des tables

Le stockage de données dans un champ JSON peut offrir de la flexibilité, mais cela présente également des défis lors de l'exécution d'opérations sur les données. . Dans cette enquête, l'objectif est de joindre une table à l'aide d'un champ JSON qui stocke un tableau d'ID.

Énoncé du problème

L'utilisateur a créé deux tables, l'utilisateur et user_group, ainsi qu'un champ JSON user_groups fictif dans la table user pour stocker une liste d'ID. L'utilisateur souhaite effectuer des opérations sur ce champ JSON et les utiliser dans SQL.

Plus précisément, l'utilisateur cherche à obtenir des résultats répertoriant les ID utilisateur, les noms d'utilisateur, les ID de groupe d'utilisateurs et les noms de groupe. Ces résultats doivent être joints en fonction des identifiants stockés dans le champ JSON.

Solution

Pour y parvenir, l'utilisateur peut exploiter la fonction JSON_CONTAINS. Cette fonction permet aux utilisateurs de vérifier si un document JSON contient une valeur ou une sous-requête spécifique.

Code

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$')</code>

Explication

• La fonction JSON_CONTAINS vérifie si le champ JSON user_groups de chaque ligne de la table user contient le user_group_id correspondant de la table user_group. Il utilise $ pour indiquer l'objet de niveau supérieur dans le document JSON.
• L'expression CAST convertit le user_group_id en chaîne JSON avant de le comparer au champ JSON.
• Le résultat de la comparaison est utilisé pour effectuer une jointure gauche entre les tables user et user_group, garantissant que toutes les lignes des utilisateurs sont incluses, même si elles n'ont pas de groupe correspondant.
• La requête sélectionne ensuite les colonnes souhaitées : user_id, user_name, user_group_id, et group_name.

Solution alternative

Si l'utilisateur préfère, il peut également utiliser la fonction JSON_TABLE pour extraire le tableau d'ID du champ JSON et créer une table virtuelle. Cette approche permet plus de flexibilité dans la manipulation des données.

Code

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       uv.user_group_id
       (
           SELECT 
               g.group_name
           FROM user_group g
           WHERE g.user_group_id = uv.user_group_id
       ) AS group_name
   FROM user u
   CROSS JOIN JSON_TABLE(
       u.user_groups,
       '$[*]' COLUMNS (user_group_id INT PATH '$')
   ) uv</code>

Explication

• Le La fonction JSON_TABLE crée une table virtuelle uv à partir du champ JSON user_groups.
• La clause CROSS JOIN garantit que chaque ligne de la table utilisateur est associée à toutes les lignes de la table virtuelle.
• La sous-requête imbriquée récupère le nom de groupe pour chaque user_group_id dans la table virtuelle uv.

En tirant parti de ces techniques, les utilisateurs peuvent utiliser efficacement les champs JSON pour joindre des tables et effectuer des opérations de données complexes dans MySQL.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!