Wie generiert man verschachtelte JSON-Objekte aus Eins-zu-Viele-Beziehungen in MySQL?

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

Dieser Artikel zeigt, wie Sie die nativen JSON-Funktionen von MySQL Version 5.7.12 verwenden, um verschachtelte Objekte zu erstellen JSON-Dokumente, die Eins-zu-viele-Beziehungen in relationalen Daten darstellen.

Problem Anweisung

Gegeben zwei Tabellen, parent_table und child_table, die eine Eltern-Kind-Beziehung darstellen, besteht das Ziel darin, ein JSON-Dokument mit der folgenden Struktur zu generieren:

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

Lösung

Anfangs führten Versuche, die verschachtelten JSON-Objekte mithilfe einfacher verschachtelter Unterabfragen zu erstellen, zu einem Fehler, da MySQL erwartet einfache Objektpaare im übergeordneten JSON-Objekt. Um dieses Problem zu lösen, verwenden wir die Funktion GROUP_CONCAT, um mehrere Unterabfrageergebnisse in einer einzigen Zeichenfolge zu verketten und dieses Ergebnis dann in ein JSON-Array zu konvertieren.

Die resultierende JSON-Ausgabe enthält jedoch Escape-Zeichen aufgrund der Behandlung der Unterabfrage als eine Zeichenfolge. Um dies zu beheben, verwenden wir die Funktionen CAST und CONCAT, um das JSON-Array direkt in der Unterabfrage zu erstellen, anstatt uns auf die Verkettung in der Hauptabfrage zu verlassen.

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 Abfrage erzeugt die gewünschte 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 Eins-zu-Viele-Beziehungen in MySQL?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!