Arbeiten mit JSON in MySQL

In der modernen Anwendungsentwicklung ist die Verwaltung halbstrukturierter Daten eine häufige Herausforderung. Die in Version 5.7 eingeführte Unterstützung von MySQL für JSON bietet eine leistungsstarke Möglichkeit, solche Daten in einer relationalen Datenbank zu speichern, abzufragen und zu bearbeiten. In diesem Beitrag werden die wesentlichen JSON-Funktionen von MySQL anhand praktischer Beispiele erläutert, die den Einstieg erleichtern.

Warum JSON in MySQL?

Das Speichern von JSON-Daten in relationalen Datenbanken kann die Handhabung halbstrukturierter oder hierarchischer Daten vereinfachen. Es ermöglicht:

• Flexibilität: JSON ermöglicht eine dynamische und hierarchische Datenspeicherung.
• Eingebaute Funktionen: JSON-Daten effizient abfragen, aktualisieren und validieren.
• Integration: Kombinieren Sie relationale Daten mit JSON-Objekten für hybride Anwendungsfälle.

1. JSON-Daten erstellen

Sie können JSON-Objekte oder Arrays mit den Funktionen JSON_OBJECT() und JSON_ARRAY() erstellen.

Beispiel:

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

Ausgabe:

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

2. Speichern von JSON-Daten

Um JSON-Daten zu speichern, verwenden Sie den JSON-Datentyp für Spalten.

Beispiel:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

3. Extrahieren von Daten aus JSON

MySQL bietet mehrere Funktionen zum Extrahieren von Daten aus JSON-Dokumenten:

• JSON_EXTRACT(): Werte mithilfe von JSON-Pfaden abrufen.
• -> Operator: Abkürzung für JSON_EXTRACT().

Beispiel:

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

Ausgabe:

4. JSON-Daten ändern

Sie können Elemente zu JSON-Daten aktualisieren oder hinzufügen mit:

• JSON_SET(): Schlüssel einfügen oder aktualisieren.
• JSON_INSERT(): Einfügen, wenn der Schlüssel nicht existiert.
• JSON_REPLACE(): Nur vorhandene Schlüssel aktualisieren.

Beispiel:

UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;

Ausgabe:

{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}

5. Schlüssel oder Werte entfernen

Verwenden Sie JSON_REMOVE(), um Elemente aus einem JSON-Dokument zu löschen.

Beispiel:

UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;

Ausgabe:

{"name": "Bob", "age": 30, "city": "New York"}

6. Suche in JSON

Die Funktion JSON_CONTAINS() prüft, ob ein JSON-Dokument einen bestimmten Wert enthält.

Beispiel:

SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;

Ausgabe:

7. Aggregieren von JSON-Daten

Die Funktionen JSON_ARRAYAGG() und JSON_OBJECTAGG() helfen dabei, Abfrageergebnisse in JSON-Strukturen zusammenzufassen.

Beispiel:

SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;

Ausgabe:

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

8. Validierung von JSON-Daten

Die Funktion JSON_VALID() prüft, ob eine Zeichenfolge gültiges JSON ist.

Beispiel:

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

Ausgabe:

9. Hübsches Drucken von JSON

Verwenden Sie JSON_PRETTY() für menschenlesbare JSON-Formatierung.

Beispiel:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

Ausgabe:

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

Zusätzliche JSON-Funktionen

MySQL bietet eine breite Palette an JSON-Funktionen, die über die im Detail beschriebenen hinausgehen. Nachfolgend finden Sie weitere JSON-Funktionen:

• JSON_ARRAY_APPEND(): Hängt Werte an das Ende eines JSON-Arrays an.
• JSON_ARRAY_INSERT(): Fügt Werte an angegebenen Positionen in ein JSON-Array ein.
• JSON_CONTAINS_PATH(): Prüft, ob ein angegebener Pfad in einem JSON-Dokument vorhanden ist.
• JSON_DEPTH(): Gibt die maximale Tiefe eines JSON-Dokuments zurück.
• JSON_KEYS(): Extrahiert die Schlüssel aus einem JSON-Objekt.
• JSON_LENGTH(): Berechnet die Anzahl der Elemente in einem JSON-Array oder die Anzahl der Schlüssel in einem JSON-Objekt.
• JSON_MERGE_PATCH(): Kombiniert mehrere JSON-Dokumente und überschreibt vorhandene Schlüssel mit den neuesten Werten.
• JSON_MERGE_PRESERVE(): Kombiniert mehrere JSON-Dokumente und behält alle Werte bei, indem Arrays für Duplikate erstellt werden.
• JSON_OVERLAPS(): Ermittelt, ob zwei JSON-Dokumente überlappende Elemente haben.
• JSON_QUOTE(): Entgeht Sonderzeichen in einer Zeichenfolge und gibt sie als gültiges JSON-Zeichenfolgenliteral zurück.
• JSON_SEARCH(): Gibt den Pfad zu einem angegebenen Wert innerhalb eines JSON-Dokuments zurück.
• JSON_STORAGE_FREE(): Gibt die Menge an Speicherplatz zurück, die nach dem Ändern einer JSON-Spalte freigegeben wurde.
• JSON_STORAGE_SIZE(): Gibt die Speichergröße eines JSON-Dokuments in Bytes an.
• JSON_TABLE(): Konvertiert JSON-Daten zur weiteren Verarbeitung in ein relationales Tabellenformat.
• JSON_TYPE(): Gibt den Typ eines JSON-Werts zurück (z. B. Objekt, Array, String usw.).
• JSON_UNQUOTE(): Entfernt Anführungszeichen aus einer JSON-Zeichenfolge.

Die JSON-Funktionen von MySQL bieten leistungsstarke Tools für die Verwaltung halbstrukturierter Daten in relationalen Datenbanken. Sie erleichtern das Speichern, Abfragen und Bearbeiten von JSON direkt in SQL. Das Verständnis dieser Funktionen kann dazu beitragen, Arbeitsabläufe zu vereinfachen und neue Ansätze für das Datenbankdesign zu eröffnen.

Das obige ist der detaillierte Inhalt vonArbeiten mit JSON in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!