listagg(distinct X,…) within group (order by X)
Die meisten Erweiterungen gab es bei Version 19c rund um das Thema JSON. So wurden zum Beispiel einige Funktionen, die im Standard definiert sind, um proprietäre Erweiterungen ergänzt.
Die erste Erweiterung betrifft die Funktion Json_object
: Sie akzeptiert nun auch Ausdrücke mit Stern (*
), ähnlich zur Select
-Klausel. Json_object(*)
erstellt also ein JSON-Objekt mit einem Attribut pro Spalte. Man kann den Stern auch mit einem Tabellennamen qualifizieren und weitere Spalten auflisten: json_object(t1.*, t2.Spalte)
. Siehe: Dokumentation.
Die zweite Erweiterung betrifft die Konvertierung zwischen benutzerdefinierten SQL-Typen (create type <udt>
) und JSON-Objekten. Dafür akzeptieren sowohl Json_object
als auch Json_array
benutzerdefinierte Typen und erstellen daraus JSON-Objekte mit allen Attributen des entsprechenden Typs. Die umgekehrte Konvertierung erfolgt mit der Funktion Json_value
, bei der man in der Returning
-Klausel nun auch benutzerdefinierte Typen angeben kann. Siehe: Dokumentation.
Da diese Funktionalitäten nicht im Standard beschrieben sind, sind sie in der folgenden Übersicht grau dargestellt.0
Mit Version 19c wurden neue JSON-Funktionen und eine verkürzte Syntax für die Funktion Json_table
eingeführt. Diese verkürzte Syntax erscheint mir der Klarheit einer Abfrage nicht sehr förderlich zu sein, sodass ich an dieser Stelle nur auf die Dokumentation verweise, anstatt ein Beispiel zu zeigen.
Die neue Funktion Json_serialize
ist laut Dokumentation vor allem nützlich, um als Blob gespeicherte JSON-Dokumente in eine Textrepräsentation überzuführen. Das wäre wohl auch mittels Json_query
möglich. Json_serialize
unterstützt jedoch einige Zusätze, die Json_query
nicht kennt. Das ist insbesondere Pretty
, um das Ergebnis hübsch zu formatieren.
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
Die interessanteste unter den neuen Funktionen ist Json_mergepatch
. Damit kann man bestehenden JSON-Objekten neue Attribute hinzufügen und bestehende ändern oder löschen. Laut Dokumentation wird dabei RFC 7396 umgesetzt, im Detail gibt es jedoch einige Abweichungen.1 Überraschend ist auch, dass die andere SQL-Datenbank aus dem Hause Oracle – MySQL – diese Funktionalität unter einem geringfügig anderen Namen anbietet: json_merge_patch
.
Die Json_mergepatch
-Funktion erwartet zwei JSON-Argumente: zuerst das Dokument, das geändert werden soll, dann das Patch-Dokument, das die Änderungen beschreibt.
JSON_MERGEPATCH('{"a": "unverändert",
"b": "ersetzen",
"c": "löschen"}'
,
'{"b": "ersetzt",
"c": null,
"d": "neu"}'
)
Das Ergebnis ist das geänderte Dokument. Attribute, die im Patch-Dokument vorhanden sind, werden im Ausgangsdokument zuvor gelöscht, wenn sie im Patch den JSON-Null-Wert haben. Andernfalls haben diese Attribute im Ergebnis den Wert, den sie auch im Patch-Dokument haben. Das Ergebnis dieses Beispiels ist daher folgendes JSON-Dokument (allerdings unformatiert):
{"a": "unverändert",
"b": "ersetzt",
"d": "neu"}
Json_mergepatch
ist dabei nicht auf flache JSON-Objekte wie in diesem Beispiel beschränkt. Es kann auch mit verschachtelten Strukturen umgehen und ganze Objekte und Arrays löschen und ersetzen. Der Zugriff auf einzelne Elemente eines Arrays ist allerdings nicht möglich.
json_merge_patch
(beachte den zweiten Unterstrich)Die SQL/JSON-Path-Sprache wird von einigen SQL-Funktionen genutzt, um auf Teile eines JSON-Dokumentes zuzugreifen – ähnlich XPath für XML oder CSS-Selektoren für HTML.
Der im SQL-Standard definierte Funktionsumfang von SQL/JSON Path wird von der Oracle Datenbank jedoch noch nicht vollständig umgesetzt. Version 19c hat jedoch einige Lücken geschlossen.
Die sogenannten Item-Methoden sind Funktionen, die man auf die von einem SQL/JSON-Path-Ausdruck identifizierten JSON-Element anwenden kann. Ab Version 19c werden nun auch die Methoden .abs()
, .ceiling()
und .floor()
unterstützt – allerdings nur in Filter-Ausdrücken (siehe unten).
Eine Sonderstellung nimmt die Funktion .double()
ein, die in Version 12.2 schon verfügbar war, in Version 18c aber nicht mehr richtig funktionierte. Dieser Fehler wurde mit 19c offenbar wieder behoben.
Eine andere auffällige Änderung betrifft die Item-Methode .size()
. Laut dem aktuellen SQL-Standard von 2016 liefert diese Methode die Anzahl der Elemente in einem JSON-Array. Wendet man diese Methode auf einen anderen JSON-Datentyp an, ist das Ergebnis immer 1. In der Oracle-Datenbank 18c hat sich diese Funktion auch so verhalten. Bei Version 19c liefert sie jedoch für JSON-Objekte nicht mehr den Wert 1, sondern die Anzahl der Attribute im Objekt. Das mag zwar ein nützliches Verhalten sein, steht jedoch im Widerspruch zum SQL-Standard und ist in der Kompatibilitätsmatrix daher mit einem roten X gekennzeichnet.
SQL/JSON Path Filterausdrücke erlauben die Verwendung von Bedingungen ähnlich zu Prädikaten in XPath ([…]
). Sie werden von einem Fragezeichen eingeleitet und erwarten die Bedingung in Klammern: ? (…)
.
Erstaunlicherweise wurden Filterausdrücke vor Version 19c generell nur in der Funktion Json_exists
unterstützt. Wollte man einen Filterausdruck in andere Funktionen wie zum Beispiel Json_query
nutzen, wurde das mit der Fehlermeldung „ORA-40553: path expression with predicates not supported in this operation“ quittiert. Das ist insofern erstaunlich, als dass der SQL-Standard keine verschiedenen SQL/JSON Path-Dialekte für einzelne Funktionen vorsieht. In Version 19c sind Filterausdrücke nun aber in allen JSON-Funktionen verfügbar.
Eine andere Einschränkung von Filterausdrücken, die mit Version 19c aufgehoben wurde, war, dass Filterausdrücke nur als letzter Schritt eines SQL/JSON Path-Ausdruckes verwendet werden konnten. Das folgende Beispiel führt nach dem Filterausdruck ? (@.a > 42)
noch einen Zugriff auf das Attribute b
durch, was vor Version 19c zu einem Syntaxfehler führte.
$ ? (@.a > 42) .b
? (…)
)Mit Version 19c wurden auch SQL/JSON Path-Funktionen eingeführt, die nicht im SQL-Standard definiert sind.
Die erste Neuerung in diesem Bereich ist ein Attributzugriff, der rekursiv in JSON-Objekte absteigt. Die Syntax ist ähnlich dem normalen Attributzugriff, nur dass zwei Punkte verwendet werden: ..<key>
.
Weiters wurden proprietäre Item-Methoden eingeführt, die auf Zeichenketten anwendbar sind: .length()
, .lower()
, .upper()
. Auch diese können in Version 19c nur in Filterausdrücken genutzt werden.
? (…)
)Die am weitesten diskutierte Änderung von Version 19c war jedoch keine neue Funktion, sondern das Entfernen einer alten. Ab Version 19c wird die Hochverfügbarkeitslösung RAC nicht mehr für die Standard Edition SE2 unterstützt. Bedenkt man, dass diese Version eigentlich nur ein Patch sein sollte, ist das schon eine äußerst überraschende Änderung. Nutzer, die eine SE2 RAC-Installation betreiben, werden früher oder später vor der unglücklichen Wahl stehen, künftig die teurere Enterprise Edition zu lizenzieren, oder auf eine andere Hochverfügbarkeitslösung umzusteigen.
Neben den hier gezeigten Neuerungen im SQL-Dialekt gab es mit Version 19c natürlich auch Änderungen in anderen Bereichen. Eine Auflistung findet sich in der Oracle-Dokumentation.
20 Jahre SQL-Evolution kann man nicht an einem Tag nachholen. Abonniere den Newsletter via E-Mail, Twitter oder RSS, um sukzessive aufzuholen und modern-sql.com am Radar zu behalten.
Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how
Json_object(*)
: SQL:2016-2: §6.33 Format für <JSON object constructor>
Json_object(<udt>)
und json_array(<udt>)
: SQL:2016-2: §6.13 SR12 (kein Cast zwischen Benutzerdefinierten Typen und Zeichenketten. Geäbe es einen eigenen JSON-Typen, wäre das eventuell anders).
Json_value(…returning <udt>)
: SQL:2016-2: §6.27 SR2
Nicht einmal die Beispiele aus Appendix A „Example Test Cases“ des RFCs funktionieren korrekt.