SQL: JSON, Textsuche und moderne SQL-Funktionen

Kurz: Moderne Datenbanken speichern und durchsuchen JSON. SQLite hat die Funktionen json_extract, json_array, json_each, -> und ->>:

Teil des Kurses SQL

Kapitel 13 von 15 im Kurs SQL. Mit Fortschritt, Quiz und Zertifikat auf der Lernseite.

JSON in SQL

Moderne Datenbanken speichern und durchsuchen JSON. SQLite hat die Funktionen json_extract, json_array, json_each, -> und ->>:

CREATE TABLE events (id INTEGER PRIMARY KEY, daten TEXT);
INSERT INTO events (daten) VALUES
 ('{"typ":"klick","nutzer":{"name":"Mia","alter":17},"tags":["a","b"]}'),
 ('{"typ":"kauf","nutzer":{"name":"Tom","alter":25},"tags":["b","c","d"],"betrag":19.9}');
 
SELECT id,
       json_extract(daten, '$.typ') AS typ,
       daten ->> '$.nutzer.name' AS nutzer,
       json_array_length(daten, '$.tags') AS anzahl_tags,
       daten ->> '$.betrag' AS betrag
FROM events;

Ausgabe:

id  typ    nutzer  anzahl_tags  betrag
--  -----  ------  -----------  ------
1   klick  Mia     2
2   kauf   Tom     3            19.9

Arrays auseinandernehmen mit json_each:

CREATE TABLE events (id INTEGER PRIMARY KEY, daten TEXT);
INSERT INTO events (daten) VALUES ('{"tags":["a","b"]}'), ('{"tags":["b","c","d"]}');
SELECT e.id, j.value AS tag
FROM events e, json_each(e.daten, '$.tags') j
ORDER BY e.id, j.key;
 
SELECT json_object('name', 'Mia', 'punkte', 120) AS obj, json_array(1, 'x', NULL) AS arr;

Ausgabe:

id  tag
--  ---
1   a
1   b
2   b
2   c
2   d
obj                          arr
---------------------------  ------------
{"name":"Mia","punkte":120}  [1,"x",null]

PostgreSQL bietet den Typ jsonb mit Operatoren ->, ->>, @> und Indizes (GIN). MySQL hat JSON_EXTRACT.

Volltextsuche (FTS5)

CREATE VIRTUAL TABLE artikel USING fts5(titel, inhalt);
INSERT INTO artikel VALUES ('SQL lernen', 'Joins und Aggregation erklärt'),
                           ('Python Grundlagen', 'Variablen und Schleifen'),
                           ('SQL Performance', 'Indizes beschleunigen Abfragen');
SELECT titel FROM artikel WHERE artikel MATCH 'sql' ORDER BY rank;
SELECT titel FROM artikel WHERE artikel MATCH 'indizes OR schleifen' ORDER BY titel;

Ausgabe:

titel
---------------
SQL Performance
SQL lernen
titel
-----------------
Python Grundlagen
SQL Performance

Window-, Datum- und String-Extras im Überblick

Wunsch	SQLite	PostgreSQL
Zufallszahl	random()	random()
Text trennen	substr, instr	split_part, string_to_array
Zeilen zu Text	group_concat	string_agg
Datum formatieren	strftime	to_char
Typ prüfen	typeof	pg_typeof
Upsert	ON CONFLICT	ON CONFLICT

Generierte Spalten und Zeilenwertsyntax

CREATE TABLE rechteck (
    breite REAL, hoehe REAL,
    flaeche REAL GENERATED ALWAYS AS (breite * hoehe) STORED
);
INSERT INTO rechteck (breite, hoehe) VALUES (3, 4), (2.5, 2);
SELECT * FROM rechteck;
SELECT (1, 2) < (1, 3) AS zeilenvergleich;

Ausgabe:

breite  hoehe  flaeche
------  -----  -------
3.0     4.0    12.0
2.5     2.0    5.0
zeilenvergleich
---------------
1

Merke

• JSON lässt sich mit json_extract, ->/->> und json_each abfragen
• FTS5 (SQLite) bzw. tsvector (PostgreSQL) ermöglichen Volltextsuche
• Generierte Spalten berechnen Werte automatisch
• Funktionsnamen unterscheiden sich je System; die Konzepte bleiben gleich

Übungsaufgabe

Speichere drei JSON-Dokumente mit Name und Tags und liste alle Tags mit json_each auf.

Quiz zur Selbstkontrolle

Wofür dient der Operator ->> in SQLite/PostgreSQL?

• Einen JSON-Wert als Text lesen (richtig)
• Zwei Tabellen verbinden
• Zeilen löschen
• Zahlen addieren

Wofür ist FTS5?

• Volltextsuche in SQLite (richtig)
• Ein Dateiformat
• Ein Verschlüsselungsverfahren
• Ein Index für Zahlen

Was machen generierte Spalten?

• Sie berechnen ihren Wert automatisch aus anderen Spalten (richtig)
• Sie erzeugen IDs
• Sie löschen Zeilen
• Sie verschlüsseln Daten

Weiter im Kurs

Zurück: Transaktionen, Views und Trigger

Weiter: Datenbankentwurf und Normalisierung

Alle Kapitel: SQL im Überblick