Wie generiert man verschachtelte JSON-Objekte aus relationalen MySQL-Daten mithilfe von JSON_OBJECT und JSON_ARRAY?

Generieren verschachtelter JSON-Objekte mit nativen MySQL-JSON-Funktionen

MySQL Version 5.7.12 führte native JSON-Funktionen ein und ermöglichte die Erstellung von JSON-Dokumenten direkt aus relationalen Daten. Um dies zu erreichen, kann man die Funktionen JSON_OBJECT() und JSON_ARRAY() nutzen.

Betrachten Sie die bereitgestellten Beispieltabellen:

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

Das Ziel besteht darin, ein JSON-Dokument mit verschachtelten Objekten zu generieren. ungefähr wie folgt:

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

Behebung des Fehlers

Vorherige Versuche ist auf den Fehler „Unterabfrage gibt mehr als eine Zeile zurück“ gestoßen, da das JSON-Objekt einfache Schlüssel-Wert-Paare als Eingaben erfordert. Unterabfragen, die mehrere Zeilen zurückgeben, müssen in einen einzelnen String oder ein JSON-Array konvertiert werden.

Lösung

Um diesen Fehler zu beheben, kann die Funktion GROUP_CONCAT() von MySQL eingesetzt werden. Dies wird durch die folgende Abfrage erreicht:

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;

Um Escape-Zeichen in der Ausgabe zu eliminieren, wird die Abfrage so geändert, dass sie die Funktionen CAST() und CONCAT() verwendet:

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;

Diese verfeinerte Abfrage erzeugt die gewünschte JSON-Ausgabe:

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

Das obige ist der detaillierte Inhalt vonWie generiert man verschachtelte JSON-Objekte aus relationalen MySQL-Daten mithilfe von JSON_OBJECT und JSON_ARRAY?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!