Wie verbinde ich MySQL-Tabellen basierend auf in einem JSON-Feld gespeicherten IDs?

MySQL-JSON-Feld zum Verknüpfen von Tabellen verwenden

Das Speichern von Daten in einem JSON-Feld kann Flexibilität bieten, stellt aber auch Herausforderungen bei der Durchführung von Datenoperationen dar . Bei dieser Anfrage besteht das Ziel darin, eine Tabelle mithilfe eines JSON-Felds zu verknüpfen, in dem ein Array von IDs gespeichert ist.

Problemstellung

Der Benutzer hat zwei Tabellen erstellt, Benutzer und user_group sowie ein fiktives JSON-Feld „user_groups“ in der Benutzertabelle zum Speichern einer Liste von IDs. Der Benutzer möchte Operationen an diesem JSON-Feld ausführen und diese in SQL verwenden.

Konkret möchte der Benutzer Ergebnisse erhalten, die Benutzer-IDs, Benutzernamen, Benutzergruppen-IDs und Gruppennamen auflisten. Diese Ergebnisse sollten basierend auf den im JSON-Feld gespeicherten IDs zusammengeführt werden.

Lösung

Um dies zu erreichen, kann der Benutzer die Funktion JSON_CONTAINS nutzen. Mit dieser Funktion können Benutzer überprüfen, ob ein JSON-Dokument einen bestimmten Wert oder eine bestimmte Unterabfrage enthält.

Code

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       g.user_group_id,
       g.group_name
   FROM user u
   LEFT JOIN user_group g on JSON_CONTAINS(u.user_groups, CAST(g.user_group_id as JSON), '$')</code>

Erklärung

• Die Funktion JSON_CONTAINS prüft, ob das JSON-Feld user_groups jeder Zeile in der Benutzertabelle die entsprechende user_group_id aus der Tabelle user_group enthält. Es verwendet $, um das Objekt der obersten Ebene im JSON-Dokument anzugeben.
• Der CAST-Ausdruck konvertiert die user_group_id in eine JSON-Zeichenfolge, bevor er sie mit dem JSON-Feld vergleicht.
• Das Ergebnis des Vergleichs wird verwendet, um eine Linksverknüpfung zwischen den Tabellen „user“ und „user_group“ durchzuführen und sicherzustellen, dass alle Benutzerzeilen enthalten sind, auch wenn sie keine passende Gruppe haben.
• Die Abfrage wählt dann die gewünschten Spalten aus: user_id, user_name, user_group_id, und Gruppenname.

Alternative Lösung

Wenn der Benutzer es vorzieht, könnte er auch die JSON_TABLE-Funktion verwenden, um das Array von IDs aus dem JSON-Feld zu extrahieren und zu erstellen ein virtueller Tisch. Dieser Ansatz ermöglicht mehr Flexibilität bei der Bearbeitung der Daten.

Code

<code class="sql">SELECT 
       u.user_id, 
       u.user_name, 
       uv.user_group_id
       (
           SELECT 
               g.group_name
           FROM user_group g
           WHERE g.user_group_id = uv.user_group_id
       ) AS group_name
   FROM user u
   CROSS JOIN JSON_TABLE(
       u.user_groups,
       '$[*]' COLUMNS (user_group_id INT PATH '$')
   ) uv</code>

Erklärung

• Der Die Funktion JSON_TABLE erstellt eine virtuelle Tabelle aus dem JSON-Feld user_groups.
• Die CROSS JOIN-Klausel stellt sicher, dass jede Zeile in der Benutzertabelle mit allen Zeilen in der virtuellen Tabelle verknüpft ist.
• Die verschachtelte Unterabfrage Ruft den Gruppennamen für jede Benutzergruppen-ID in der virtuellen UV-Tabelle ab.

Durch die Nutzung dieser Techniken können Benutzer JSON-Felder effektiv zum Verbinden von Tabellen und zum Durchführen komplexer Datenoperationen in MySQL nutzen.

Das obige ist der detaillierte Inhalt vonWie verbinde ich MySQL-Tabellen basierend auf in einem JSON-Feld gespeicherten IDs?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!