Was ist neu in SQL:2016

Im Dezember 2016 hat ISO eine neue Version des internationalen SQL-Standards herausgebracht (ISO/IEC 9075:2016). Sie ersetzt die vorherige Version von 2011.

Dieser Artikel gibt einen kurzen Überblick über die neuen SQL Funktionen. Genauer gesagt behandelt dieser Artikel nur die Neuerungen in Teil 2 des Standards (SQL/Foundation) – das ist der Hauptteil des Standards.

Weiters zeigt dieser Artikel die Verfügbarkeit der neuen Funktionen in sechs gängigen Datenbaken. Die entsprechenden Abbildungen – siehe unten – spiegeln jedoch nur die Verfügbarkeit der Funktionen, wie sie der SQL-Standard beschreibt, wieder. Ein X in der Zeile JSON bedeutet also nicht, dass diese Datenbank keine JSON-Unterstützung hat. Es bedeutet lediglich, dass die im Standard beschriebenen Funktionen nicht unterstützt werden. Tatsächlich kann jede der sechs Datenbanken mit JSON umgehen – aber jeder auf andere Weise.0

1. In der from-Klausel
2. Einige Funktionen sind „zufällig“ konform.
3. Weitgehend.
4. Keine on overflow-Klausel • Beschränktes distinct

Bevor wir uns die neuen Funktionen ansehen, noch ein paar Statistiken: Teil 2 des SQL-Standards hat mit 1732 Seiten um 260 Seiten (~18%) mehr als der Voränger. Es wurden 44 neue optionale Funktionen (+14%) eingeführt. Und diese wären…

Zeilenmustererkennung

Zeilenmustererkennung identifiziert Gruppen von Zeilen, deren Abfolge einem Muster entspricht. Zur Beschreibung des Musters wird ein regulärer Ausdruck verwendet.. Zeilengruppen, die einem Muster entsprechen, können gefiltert, gruppiert und aggregiert werden.

Die Hauptanwendung der Zeilenmustererkennung ist Analyse von Zeitreihen. Da die neue match_recognize-Klausel jedoch Funktionen von where, group by, having und over vereint, ist sie auch bei vielen anderen Problemen nützlich.

Im März habe ich einen Vortrag zur Zeilenmustererkennung gehalten. Darin werden einige Beispiele in zwei Umsetzunsvarianten verglichen: einmal mit, und einmal ohne die neue match_recognize-Klausel. Die Beispiele zeigen sowohl typische, als auch atypische Anwendungen:

• Zusammenhängende Ereignisse: Sitzungen in einem Web-Log identifizieren und wie man dabei Unterbrechungen toleriert.

• Top-N pro Gruppe (kann schneller als Window-Funktionen sein!)

• Zeitintervalle: Löcher finden (Terminvergabe)

• Zeitintervalle: Löcher füllen (neue Zeilen generieren)

Die Folien zum Vortrag sind hier.

Weiterführende Informationen gibte es auch im technischen Report “Row Pattern Recognition in SQL” (ISO/IEC TR 19075-5:2016) (gratis download bei ISO).

JSON

Seit nunmehr 18 Jahren unterstützt SQL Arrays, Multisets (verschachtelte Tabellen) und zusammengesetzte Typen (Objekte). In 2003 wurde ein eigener XML-Typ eingeführt. Jetzt gibt es auch JSON-Unterstützung.

Im Folgenden werden die wichtigsten Aspekte der neuen JSON-Unterstützung kurz erklärt. Weitere Details sind im technischen Report von ISO.

Kein nativer JSON-Typ

Obwohl sich XML und JSON etwas ähneln – sie sind Dokumente mit verschachtelten Strukturen – ist die Integration in SQL sehr unterschiedlich. Der auffälligste Unterschied ist, dass der Standard im Gegensatz zu XML keinen nativen JSON-Typ einführt. Stattdessen verwendet der Standard Strings, um JSON-Daten zu speichern. Update: ISO/IEC 9075-2:2023 führte einen JSON-Typen ein (T801).

Das beutet aber nicht, dass Datenbankhersteller keinen JSON-Typ bereitstellen dürfen. Der Standard definiert lediglich eine Reihe von Funktionen, die Strings als JSON-Daten interpretieren. Datenbanken mit standardkonformer JSON-Unterstützung müssen diese String-Funktionen bereitstellen. Datenbanken können dennoch einen JSON-Typen mit den entsprechenden Funktionen anbieten. Sie können sogar die Standardfunktionen für ihren JSON-Typen bereitstellen.

Die einzig unangenehme Konsequenz, Strings für JSON-Daten zu verwenden, ist die Fehlerbehandlung.1 Gäbe es einen JSON-Typ, könnten Parsingfehler nur bei der Typenumwandlung (cast) auftreten. Stattdessen definiert der Standard eine on error-Klausel für alle Funktionen, die einen String als JSON-Daten interpretieren. Mit dieser Klausel kann man das Verhalten bei fehlerhaften JSON-Daten steuern:

json_value(json, '$.id' null on error)

Die Voreinstellung ist error or error. Der String '$.id' ist ein JSON-Pfad.

Natürlich ist es trotzdem möglich, eine Tabellenspalte anzulegen, die nur gültige JSON-Daten annimmt:

CREATE TABLE … (
   jcol CLOB CHECK (jcol IS JSON)
)

Der Check-Constraint verwendet das neue is json-Prädikat, um zu prüfen, ob der String gültige JSON-Daten enthält. Das is json-Prädikat kann auch auf spezielle JSON-Untertypen prüfen und ist damit flexibler als ein einzelner Datentyp für alle JSON-Daten:

<expr> is [not] json [ value | array | object | scalar ]

1. Siehe Alternativen
2. Akzeptiert Objekt-Schlüssel ohne Anführungszeichen: {a: 1} • Keine Typeneinschränkungen: … is json [array|object|scalar]
3. Kein unknown on error. Keine Ausdrücke in default ... on [ error | empty ]

JSON Formate

Der SQL-Standard erlaubt es, Datenbanken mehrere JSON-Formate anzubieten. Das in RFC 7159 beschriebene Format muss als Voreinstellung unterstützt werden.

Alle Funktionen, die JSON-Daten lesen oder erzeugen, akzeptieren die optionale format-Klausel (default: format json). Datenbanken können auch syntaktisch völlig unterschiedliche Formate anbieten (z. B. BSON).

JSON-Daten erzeugen

Der Standard definiert die folgenden Funktionen zum erstellen von JSON-Strukturen:

json_object([key] <expr> value <expression> [,…])

Erzeugt ein JSON-Objekt. Die Attributnamen und -werte werden von den Schlüsselwörtern key (optional) und value eingeleitet:

json_object( key 'id'   value 1234
           ,     'name' value 'Markus')

Alternativ kann einen Doppelpunkt (:) zwischen Attributname und -wert verwenden:2

json_object( 'id': 1234
           , 'name': 'Markus')

Das Komma dient lediglich zum Auflisten mehrere Attribute (Key/Value-Paare). Das ist ein wesentlicher Unterschied zu den Funktionen json_object in MySQL und SQLite oder json_build_object in PostgreSQL: diese Benutzen das Komma auch zur Trennung des Attributnamens vom Wert.

json_array([<expr>[,…]])

Erzeugt ein JSON-Array mit den angegebenen Werten.

json_array(<query>)

Erzeugt aus dem Ergebnis der Abfrage ein JSON-Array. Die Abfrage muss genau eine Spalte liefern. Die Reihenfolge der Elemente kann mit der order by-Klausel in der Abfrage gesteuert werden.

json_arrayagg(<expr> [order by …])

Erzeugt ein JSON-Array aus den Werten einer Zeilengruppe (analog zu array_agg):

SELECT json_arrayagg(col [order by seq])
  FROM …
 GROUP BY x

Die optionale order by-Klausel3 ist in den Klammern – nicht wie bei listagg in einer within group-Klausel.

json_objectagg([key] <expr> value <expression>)

Erzeugt ein JSON-Objekt aus den Attributnamen und -werten aus einer Zeilengruppe. Dabei kommt dieselbe Syntax wie bei json_object zur Anwendung:

SELECT json_objectagg(k value v)
  FROM …
 GROUP BY x

Da JSON-Object ungeordnet sind, gibt es keine order by-Klausel.

1. Keine Doppelpunkt-Syntax (T814)
2. Keine Doppelpunkt-Syntax (T814) • Kein Eindeutigkeitsprüfung der Attributnamen (T830)
3. Benötigt vor Version 3.38.0 die Compiler-Option -DSQLITE_ENABLE_JSON1
4. Voreinstellung: absent on null • Nicht mit einer Abfrage: json_array(select …)
5. Nicht mit einer Abfrage: json_array(select …)
6. Voreinstellung: absent on null • Nicht mit einer Abfrage: json_array(select …) • Benötigt vor Version 3.38.0 die Compiler-Option -DSQLITE_ENABLE_JSON1
7. Kein (ABSENT | NULL) ON NULL

Auf JSON-Elemente zugreifen

Die folgenden Funktionen nutzen die sogenannte SQL/JSON path language (siehe unten) um auf Teile eines JSON-Dokumentes zuzugreifen. Diese Funktionen interpretieren einen String als JSON-Dokument und akzeptieren daher die on error-Klausel.

json_exists(<json>, <path>)

Prüft ob der angegebene Pfad im JSON-Dokument existiert. Liefert true, false oder unknown und kann daher direkt in der where-Klausel genutzt werden:

WHERE json_exists(jcol, '$.name')

json_value(<json>, <path> [returning <type>])

Extrahiert einen skalaren JSON-Wert – also kein Objekt oder Array – und liefert ihn als nativen SQL-Wert. Die optionale returning-Klausel führt eine entsprechende Typenumwandlung durch. Ohne returning-Klausel liefert json_value einen String.

json_query(<json>, <path> …)

Extrahiert einen Teil eines JSON-Dokumentes und liefert ihn als JSON-String. Die Hauptunterschiede zu json_value sind: (1) json_query kann auf jeden JSON-Typ zugreifen; (2) json_query liefert immer einen String; (3) json_query kann mehrere Elemente aus einem JSON-Dokument extrahieren.

Aufgrund dieser Unterschiede gibt es zwei Spezialfälle:

Mehrere Treffer

Per Default liefert json_query (wie json_value) einen Fehler, wenn der JSON-Path mehrere Treffer hat. Die optionale with [ conditional | unconditional ] [array] wrapper-Klausel packt die Ergebnisse in ein JSON-Array und liefert alle Treffer.

JSON path bezeichnet einen String

Json_query liefert generell JSON-Daten. Wenn der JSON-Pfad einen einzelnen String trifft, ist das Ergebnis noch immer ein JSON-String: JSON-Sonderzeichen werden maskiert, der ganze String ist in Anführungszeichen. Die omit quotes [on scalar string]-Klausel liefert stattdessen die Rohdaten (wie json_value).

Letztendlich gibt es noch die Funktion json_table: Sie ist im Wesentlichen eine Tabellenfunktion, die in der from-Klausel verwendet wird.

json_table(<json>, <path> columns …)

Erzeugt aus den Daten eines JSON-Dokuments eine Tabelle. json_table führt keine neue Funktionalität ein, ist aber oft einfacher (und schneller) als die entsprechenden json_value-Aufrufe.4

In ISO’s technischem Report (Absatz 5.3.4) gibt es eine ausführliche Beschreibung dieser Funktion. Das folgende Beispiel dient lediglich als Vorgeschmack. Es übersetzt ein JSON-Object (Array mit zwei Objekten) in eine Tabelle:

[{id: 1, name: "Marvin"},
 {id: 2, name: "Arthur"}
]

SELECT jt.*
  FROM t
     , JSON_TABLE
       ( jcol
       , '$[*]'
         COLUMNS (id   NUMERIC      PATH '$.id',
                  name VARCHAR(255) PATH '$.name'
                 )
       ) jt

Die Funktion json_table erzeugt eine Zeile für jedes Element, das dem JSON-Pfad ($[*]) entspricht. Die columns-Klausel legt die Typen und Namen der Spalten fest und deklariert über einen weiteren JSON-Pfad (relativ zum Hauptpfad) den eigentlichen Wert.

id	name
1	Marvin
2	Arthur

1. Defaults to error on error • Returning-Klausel mit eingeschränkter Funktionalität
2. Returning-Klausel mit eingeschränkter Funktionalität
3. Benötigt vor Version 3.38.0 die Compiler-Option -DSQLITE_ENABLE_JSON1
4. Kein Strings als Rohdaten: [ keep | omit ] quotes
5. with unconditional wrapper ist Fehlerhaft.
6. Alternative: json_query_array, unnest and json_value
7. Ohne Plan-Klausel • Erfordert error on error und einen stricten JSON/Path für den Hauptausdruck
8. Ohne Plan-Klausel
9. Alternative: openjson
10. Alternative: json_each and json_extract

JSON-Pfade

Der SQL-Standard definiert eine Pfad-Sprache für JSON.5 Sie setzt zahlreiche Funktionen von ECMAscript um, ist aber weder ein Sub- noch ein Superset von ECMAscript.6

In der JSON-Pfad-Sprache steht das Dollarzeichen ($) das aktuelle Element, der Punkt (.) für einen Objekt-, und die eckige Klammer ([]) für einen Arrayzugriff. Die folgenden Beispiele verdeutlichen das:

$.name

Bezeichnet das Attribute name des aktuellen JSON-Objektes.

$[0]

Bezeichnet das erste Element des aktuellen JSON-Arrays.

$.events[last]

Bezeichnet das letzte Arrayelement des Attributes events des aktuellen JSON-Objektes.

$.events[0 to 4, last]

Bezeichnet die ersten fünf und das letzte Array-Element des Attributes events des aktuellen JSON-Objektes.

$.*

Bezeichnet alle Attribute des aktuellen JSON-Objektes.

JSON-Pfade unterstützen Filterausdrücke der Form ?(<Ausdruck>). In Filtern bezeichnet das At-Zeichen (@) das aktuelle Element.

$.* ?(@.type()=="number")

Bezeichnet alle Elemente vom Typ number.

$.events[*] ?⁠(exists(@.name))

Bezeichnet alle Arrayelemente des Attributes events, welche das Attribute name enthalten.

$?⁠(@.name starts with "Lorem")

Bezeichnet das aktuelle JSON-Element, wenn der Wert des Attributes name mit Lorem beginnt.

Weiters sind die folgenden Funktionen in Filtern zulässig: size() (Array-Größe), ceiling(), floor(), abs() und datetime() (zum Parsen – siehe unten).

SQL/JSON-Pfade arbeiten in zwei Modi: lax (Voreinstellung) oder strict. Der Modus kann am Begin des Pfades angegeben werden (z. B. 'strict $.name'). Im Modus strict löst jeder Fehler die Fehlerbehandlung aus (entsprechend der on error-Klausel). Das gilt für den Zugriff auf nicht vorhandene Attribute oder die Verwendung eines skalaren Wertes als Array oder Objekt.

Im Modus lax werden solche Fehler unterdrückt. Dazu werden skalare Werte nötigenfalls in ein Array gepackt oder ein Array ausgepackt, damit die Pfadangabe der Dokumentenstruktur entspricht. Im Modus lax kann man mit variablen Dokumentenstrukturen arbeiten, ohne den JSON-Pfad an jedes Dokument anpassen zu müssen.

1. Kein Wildcard (.*)
2. Keine Anführungszeichen (."…")
3. Keine Ausdrücke • Kein last • Keine Bereiche ([… to …])
4. Keine Ausdrücke • Kein last • Keine Bereiche ([… to …]) • Keine Auflistungen (T833)
5. Keine Ausdrücke • Keine Auflistungen (T833)
6. Keine Ausdrücke
7. Keine Ausdrücke • Kein last • Keine Bereiche ([… to …]) • Keine Auflistungen (T833) • Kein Wildcard ($[*])
8. Vergleiche können keine zwei Ausdrücke haben • Like_regex kennt keine Flags • Kein (…) is unknown
9. Kein .datetime() (aber .date() und .timestamp()) • Nur in ?(…): abs, ceiling, floor, double • Kein .keyvalue()
10. Kein unwrap • Kein wrap
11. SQL/JSON path Syntaxfehler werde nicht gemeldet (nicht einmal mit ERROR ON ERROR)
12. Kein unwrap • Wildcard Arrayzugriff (.[*]) macht kein wrap • SQL/JSON path Syntaxfehler werden nicht gemeldet
13. Kein unwrap
14. Filter-Ausdrücke (? (…)) machen kein unwrap
15. SQL/JSON path Syntaxfehler werden nur mit ERROR ON ERROR gemeldet • Falsche SQLSTATEs
16. Falsche SQLSTATEs

Was fehlt in SQL/JSON

Der SQL-Standard stellt keine Funktionen zum Ändern von JSON-Daten zur Verfügung (wie zum Beispiel json_set von MySQL, PostgreSQL und SQLite oder json_modify von SQL Server). Ein update kann also immer nur das ganze JSON-Dokument ersetzen.

Zeitangaben formatieren und parsen

Das Formatieren und Parsen von Zeitangaben (Datum, Uhrzeit) ist eine jener Lücken des SQL-Standards, die von so ziemlich jedem Hersteller gefüllt wurde. Natürlich hat sie jeder anders gefüllt. SQL:2016 hat nun Funktionen dafür definiert.

Der SQL-Standard verwendet dafür Format-Templates wie zum Beispiel 'YYYY-MM-DD'. Die folgende Tabelle zeigt die standardisierten Mnemonics – beachte die auffällige Ähnlichkeit zu den „datetime format models“ der Oracle-Datenbank.

Beachte, dass extract (gutes, altes SQL-928) auch auf Zeitkomponenten zugreifen kann. Ich habe die entsprechenden extract Feldnamen daher in die Tabelle aufgenommen.

Diese Formattemplates können in der JSON-Pfad-Funktion datetime (siehe oben) und in einem cast verwendet werden:

CAST(<datetime> AS <char string type> [FORMAT <template>])
CAST(<char string> AS <datetime type> [FORMAT <template>])

1. Bietet .date() und .timestamp()
2. Keine Zeitzonen.
3. Keine Zeitzonen. SECOND ohne Buchteile. Verwende SECOND_MICROSECOND.

Listagg

Listagg ist eine Ordered-Set-Funktion, die den Funktionen group_concat und string_agg entspricht, wie sie einige Datenbanken anbieten. Listagg transformiert Werte einer Zeilengruppe in einen String mit konfigurierbarem Separator.

Die minimale Syntax lautet:

LISTAGG(<expr>, <separator>) WITHIN GROUP(ORDER BY …)

Listagg hat eine optionale on overflow-Klausel, um das Verhalten bei einem Überlauf zu steuern:

LISTAGG(<expr>, <separator> ON OVERFLOW …)

Die Voreinstellung ist on overflow error. Mit on overflow truncate wird der Überlauf verhindert, indem nur so viele Elemente an den String gefügt werden, wie ohne Überlauf Platz haben. Weiters kann man mit der on overflow truncate-Klausel steuern, wie das Ergebnis bei einem Überlauf abgeschlossen wird:

ON OVERFLOW TRUNCATE [<filler>] WITH[OUT] COUNT

Der optionale <filler> wird als letztes Element an den String angefügt (default: ...). Mit with count kann man zusätzlich die Anzahl der verworfenen Elemente eingeklammert an das Endergebnis setzen lassen.

Ich habe einen detaillierten Artikel über listagg geschrieben. Bitte beachte auch die typensicheren Alternativen, die für viele Anwendungen die bessere Wahl sind.

1. Wenn nach den Werten sortiert wird: listagg(distinct X,…) within group (order by X)

Trigonometrische und logarithmische Funktionen

SQL:2016 führt trigonometrische Funktionen (sin, cos, tan, asin, acos, atan, sinh, cosh, tanh), eine allgemeine logarithmische Funktion (log(<basis>, <wert>)) und eine Kurzform für den Logarithmus zur Basis 10 ein (log10(<wert>)).

Der natürliche Logarithmus ln(<wert>) wurde bereits mit SQL:2003 eingeführt.

1. SQL Server erwartet die Argumente in umgekehrter Reihenfole: LOG(<wert>, <basis>)

Polymorphe Tabellenfunktionen

SQL Tabellenfunktionen – eingeführt mit SQL:2003 – sind Funktionen, die eine Tabelle als Ergebnis liefern. Tabellenfunktionen müssen die Namen und Typen der Ergebnisspalten – den sogenannten Zeilentyp – in der Deklaration festlegen:

CREATE FUNCTION <name> (<parameters>)
       RETURNS TABLE (c1 NUMERIC(10,2), c2 TIMESTAMP)
   ...

Diese Funktion liefert also eine Tabelle mit zwei Spalten: c1 und c2 mit dem jeweils entsprechenden Typen.

Nach dem Anlegen können Tabellenfunktionen in der from und join-Klausel verwendet werden.9

Prominente Beispiele für Tabellenfunktionen sind:

• Oracle’s dbms_xplan.display_cursor (und Artverwandte)

• PostgreSQL’s generate_series

SQL:2016 führt polymorphe Tabellenfunktionen (PTF) ein: Diese müssen den Ergebnistyp nicht vorab festlegen. Stattdessen können sie eine describe component procedure bereitstellen, die den Ergebnistyp zur Laufzeit festlegt. Weder der Autor der PTF, noch der Nutzer müssen die Spalten Typen oder Namen vorweg festlegen.

Die in SQL:2016 beschriebenen PTFs werden derzeit von keiner der getesteten Datenbanken unterstützt.

Interessierte Leser sei der technische Report „Polymorphic table functions in SQL“ empfohlen. Unter anderem beschreibt der Report die folgenden Beispiele:

• CSVreader: Die Spalten werden von der ersten Zeile einer CSV-Datei festgelegt.

• Pivot (eigentlich unpivot): macht aus Spalten Zeilen (z. B. phonetype, phonenumber).

• TopNplus: liefert die ersten N Zeilen und eine Weitere mit der Summe über die restlichen Zeilen.

Ich persönlich denke, dass man die Beispiele des Reports auch mit anderen SQL-Mitteln umsetzen könnte.10 Die Umsetzung der Funktion json_table als PTF wäre vielleicht ein interessantes Beispiel gewesen. Wie auch immer: die Beispiele im Report sind weniger dazu gedacht nützlich zu sein, als die Möglichkeiten zu zeigen.

Sonstige Neuerungen

Join … using akzeptiert nun die as-Klausel (F404):

FROM A
JOIN B USING (…) AS correlation_name

Der Typ decfloat[(<precision>)] wurde neu eingeführt (T076).

Benannte Argumente in Funktions- und Prodzeduraufrufen (name => value) sind nicht mehr auf call-Answeisungen beschränkt (T524).

Eine default-Klausel für Funktionen (T525) und inout-Argumente (T523).

1. Join ... using wird generell nicht unterstützt

Funktionstaxonomie: obsolete und neue Funktionen

Der Vollständigkeit halber: Teil ISO/IEC 9075-2:2016 führt keine neuen, zwingenden Funktionen ein.11

Die Funktion T581, “Regular expression substring function” wurde als veraltet (deprecated) markiert („The functionality is essentially subsumed by Feature F844, “SUBSTRING_REGEX”“12). Beachte, dass die Funktion T141, “SIMILAR predicate” bereits seit SQL:2011 als veraltet gilt.13

Die folgende Tabelle zeigt die neuen, optionale Funktionen (im Vergleich zu 2011).