Comment générer des objets JSON imbriqués à partir de données relationnelles MySQL à l'aide de JSON_OBJECT et JSON_ARRAY ?

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

MySQL version 5.7.12 a introduit des fonctions JSON natives, permettant la création de documents JSON directement à partir de données relationnelles. Pour y parvenir, on peut exploiter les fonctions JSON_OBJECT() et JSON_ARRAY().

Considérez les exemples de tableaux fournis :

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`)
);

Le but est de générer un document JSON avec des objets imbriqués, ressemblant à ce qui suit :

[{
    "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"
        }
    ]
}]

Résolution de l'erreur

Les tentatives précédentes ont rencontré l'erreur "La sous-requête renvoie plus d'une ligne" car l'objet JSON nécessite de simples paires clé-valeur comme entrées. Les sous-requêtes qui renvoient plusieurs lignes doivent être converties en une seule chaîne ou un tableau JSON.

Solution

Pour surmonter cette erreur, la fonction GROUP_CONCAT() de MySQL peut être utilisée. La requête suivante accomplit cela :

select json_object(
  'id',p.id 
 ,'desc',p.`desc`
 ,'child_objects',json_array(
                     (select GROUP_CONCAT(
                                 json_object('id',id,'parent_id',parent_id,'desc',`desc`)
                             )   
                      from child_table 
                      where parent_id = p.id))
                   )
 from parent_table p;

Pour éliminer les caractères d'échappement dans la sortie, la requête est modifiée pour utiliser les fonctions CAST() et CONCAT() :

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 affinée produit la sortie JSON souhaitée :

'{\"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!