Comment générer du JSON imbriqué à partir de tables relationnelles dans MySQL 5.7 ?

Génération de JSON imbriqué avec les fonctions natives MySQL

Problème :

Utilisation des fonctions JSON de MySQL 5.7.12, comment pouvez-vous construire un document JSON avec des sous-objets intégrés à partir de données en relation tables ?

Configuration de la base de données :

Considérez les tables suivantes :

CREATE TABLE `parent_table` (
   `id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`)
);
CREATE TABLE `child_table` (
   `id` int(11) NOT NULL,
   `parent_id` int(11) NOT NULL,
   `desc` varchar(20) NOT NULL,
   PRIMARY KEY (`id`,`parent_id`)
);
insert `parent_table` values (1,'parent row 1');
insert `child_table` values (1,1,'child row 1');
insert `child_table` values (2,1,'child row 2');

JSON souhaité Sortie :

[{
    "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 :

Pour générer le JSON imbriqué souhaité, vous pouvez utiliser la fonction GROUP_CONCAT de MySQL pour concaténer plusieurs lignes de la table_enfant dans un chaîne. Ensuite, enveloppez la chaîne dans un JSON_ARRAY pour la convertir en tableau JSON.

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 générera le document JSON exactement comme spécifié dans la sortie souhaitée.

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!