Comment générer des objets JSON imbriqués à partir de relations un-à-plusieurs dans MySQL ?

Génération d'objets JSON imbriqués avec les fonctions JSON natives MySQL

Cet article montre comment utiliser les fonctions JSON natives de MySQL version 5.7.12 pour construire des objets imbriqués. Documents JSON représentant des relations un-à-plusieurs dans le domaine relationnel data.

Énoncé du problème

Étant donné deux tables, parent_table et child_table, représentant une relation parent-enfant, l'objectif est de générer un document JSON avec la structure suivante :

[
    {
        "id": 1,
        "desc": "parent row 1",
        "child_objects": [
            {
                "id": 1,
                "parent_id": 1,
                "desc": "child row 1"
            },
            {
                "id": 2,
                "parent_id": 1,
                "desc": "child row 2"
            }
        ]
    }
]

Solution

Dans un premier temps, tente de créer les objets JSON imbriqués à l'aide de simples sous-requêtes imbriquées entraînait une erreur car MySQL attend des paires d'objets simples dans l'objet JSON parent. Pour résoudre ce problème, nous utilisons la fonction GROUP_CONCAT pour concaténer plusieurs résultats de sous-requête en une seule chaîne, puis convertir ce résultat en un tableau JSON.

Cependant, la sortie JSON résultante contient des caractères d'échappement en raison du traitement de la sous-requête comme une chaîne. Pour remédier à cela, nous utilisons les fonctions CAST et CONCAT pour créer le tableau JSON directement dans la sous-requête, plutôt que de nous fier à la concaténation dans la requête principale.

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',(select CAST(CONCAT('[',
                GROUP_CONCAT(
                  JSON_OBJECT(
                    'id',id,'parent_id',parent_id,'desc',`desc`)),
                ']')
         AS JSON) from child_table where parent_id = p.id)

 ) from parent_table p;

Cette requête produit le résultat souhaité :

'{\"id\": 1, 
\"desc\": \"parent row 1\", 
\"child_objects\": 
    [{\"id\": 1, 
    \"desc\": \"child row 1\", 
    \"parent_id\": 1
    }, 
    {\"id\": 2, 
    \"desc\": \"child row 2\", 
    \"parent_id\": 1
    }]  
}'

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!