JSON in der Oracle-Datenbank mit NoSQL-APIs und Datenbank-API für MongoDB

Dass sich die Oracle-Datenbank sehr gut zum Speichern und Verarbeiten von heterogenen Daten und somit auch für JSON-Daten eignet, ist allgemein bekannt – jedoch ist nicht jeder Entwickler bereit, hierfür SQL zu nutzen. Hier hilft eine Document-Store-API, auch Simple Oracle Document Access (SODA), die einfache Datenbank-Operationen automatisch in äquivalentes SQL übersetzt und somit eine NoSQL-Schnittstelle zu einer relationalen Datenbank bereitstellt. Mit SODA kann man Collections in der Oracle-Datenbank erstellen und speichern, sie abrufen und abfragen, ohne SQL zu kennen oder zu wissen, wie die Dokumente in der Datenbank gespeichert sind. Dokumente können damit aus der Datenbank durch Key Lookup oder durch Query-by-Example (QBE) Pattern-Matching abgerufen werden. Oracle-SODA-Implementierungen sind in Node.js, Java, PL/SQL, Oracle Call Interface und auch über REST-Schnittstellen verfügbar – unabhängig davon, wie die Datenbank eingesetzt wird – on-premises oder in der Cloud. Sind spezifische Treiber zur Verwendung notwendig, wie zum Beispiel im Fall von Python oder Node.js, sollten diese vorab installiert werden. Alle Informationen hierzu finden sich auf der SODA-Website [1]. Kostenfreie Werkzeuge wie Oracle SQLcl und das graphische Werkzeug Database Actions komplementieren das Angebot.

Zu den Best Practices gehört, einen Datenbankbenutzer mit Speicherquotas, CREATE SESSION- und CREATE TABLE-Privilegien und EXECUTE-Privilegien auf das spezielle PL/SQL-Package DBMS_SODA anzulegen. Fortgeschrittene Benutzer, die Oracle-Sequences für Schlüssel verwenden, benötigen auch das CREATE SEQUENCE-Privileg. Und schon kann man loslegen.

Im ersten Beispiel verwenden wir das Linemode-Werkzeug Oracle SQLcl, um eine Collection mynewcollection zu erzeugen.

SQL> soda create mynewcollection;

Das Äquivalent mit einer typischen REST-Implementierung nutzt dazu das PUT-Verb mit URL
http://localhost:8080/ords/soe/soda/latest/mynewcollection. Mit diesem REST-Request wird dann eine Collection mynewcollection im Datenbankschema SOE erstellt. Generell werden Operationen wie Erstellen, Lesen, Aktualisieren und Löschen in SODA for REST auf HTTP-Verben POST, GET, PUT bzw. DELETE abgebildet.

Konzeptionell werden damit JSON-Collections auf Tabellen abgebildet, die eine JSON-Spalte und einige Metadaten besitzen. Ab Oracle Database 21c steht zur Speicherung ein optimierter JSON-Datentyp zur Verfügung, der JSON in einem Binärformat ablegt, welches für Datenbanken optimiert wurde. Das Schema der Tabelle, d. h. die Spalten und deren Datentypen ist dabei fest definiert und muss nicht vom Nutzer definiert werden. Der Befehl zum Erstellen der Collection wird automatisch in ein CREATE TABLE-Statement übersetzt, welches eine Tabelle mit den folgenden Spalten erzeugt.

SQL> describe mynewcollection

Name          Null?    Type          
------------- -------- ------------- 
ID            NOT NULL VARCHAR2(255) 
CREATED_ON    NOT NULL TIMESTAMP(6)  
LAST_MODIFIED NOT NULL TIMESTAMP(6)  
VERSION       NOT NULL VARCHAR2(255) 
JSON_DOCUMENT          JSON   

Möchte man einen Überblick über alle verfügbaren SODA-Kommandos erhalten, kann man das Kommando help verwenden.

SQL> soda help
SODA Help 
------
…
docs.oracle.com/en/database/oracle/simple-oracle-document-access/ 
        soda create <collection_name> 
              Create a new collection. 
              /* Example: create a collection named fruit */ 
              soda create fruit 

        soda insert <collection_name> <json_str | filename>  
              Insert a JSON document into a collection. 

              /* Example: insert a JSON document into a collection named fruit */ 
              soda insert fruit {"name" : "orange", "quantity" : 20} 

        soda get <collection_name> [-all | -f | -k | -klist] [{<key> | <k1> <k2> ... > | <qbe>}] 
            List documents the collection 
            Optional arguments:  
             -all    list the keys of all docs in the collection  
             -k      list docs matching the specific <key> 
             -klist  list docs matching the list of keys 
             -f      list docs matching the <qbe> 
            /* Example: Get all documents from the collection named fruit. */ 
            soda get fruit 

            /* Example: Get the documents in the collection named fruit where the 
               "name" attribute is equal to  "orange" */ 
            soda get fruit -f {"name" : "orange"} 

            /* Example: Get the documents in the collection named fruit where the 
               "name" attribute is equal to  "orange" */ 
            soda get fruit -f {"quantity" : {"$lt" : 50}} 

       soda remove <collection_name> [-k | -klist |-f] {<key> | <k1> <k2> ... | <qbe>} 
              Remove documents from the collection. 
...

Befehle zum Einfügen von JSON-Dokumenten werden gleichermaßen als ein SQL-INSERT ausgeführt. Ähnliches gilt für UPDATEs und DELETEs. Ein Einfügen mittels SODA sieht folgendermaßen aus.

soda insert mynewcollection {"name": "John", "address": {"city": "Georgetown", "street": "Alexa Place 40"}}

Eine wichtige Eigenschaft der Document-Store-APIs ist, nach einem oder mehreren Dokumenten suchen zu können, die definierten Suchkriterien entsprechen. Dabei wird die Anfrage selbst als ein JSON-Dokument dargestellt. Das folgende Beispiel selektiert alle Dokumente, bei denen der Name Matilda und im Adressfeld die Stadt Sydney vorkommt.

soda get mynewcollection -f {"name": "Matilda","address": {"city": "Sydney"}}  

Einfacher geht der Zugriff über das graphische Werkzeug Database Actions {}JSON, das automatisch mit Oracle Autonomous Database Cloud Services bereitgestellt wird, aber auch über jede vom Kunden verwaltete ORDS (Oracle REST Data Services)-Installation und deren Oracle-Datenbanken zur Verfügung gestellt werden kann. Database Actions wird in ORDS ausgeführt – mit Zugriff über eine schema-basierte Authentifizierung. Um auf Database Actions zuzugreifen, muss man sich als Datenbankbenutzer anmelden, dessen Schema für Database Actions aktiviert wurde. In Oracle Autonomous Database ist der Benutzer ADMIN bereits aktiviert. Um das Schema eines anderen Datenbankbenutzers (hier z. B. SOE) zu aktivieren, kann man folgenden Code als privilegierter Benutzer wie z. B. ADMIN verwenden.

begin
ords_admin.enable_schema(p_enabled => TRUE,
p_schema => 'soe',
p_url_mapping_type => 'BASE_PATH', 
p_url_mapping_pattern => 'SOE',
p_auto_rest_auth => NULL );
commit;
end;
/

Danach kann man sich über die Oberfläche mit SOE einloggen und eine Anfrage mit folgenden JSON-Informationen ausführen: { "name": "John", "address": {"city": "Georgetown"}}

Die Anfrage wird automatisch in ein SELECT-Statement übersetzt, in dem der SQL/JSON-Operator JSON_EXISTS die passenden Dokumente filtert.

select * from mynewcollection 
where JSON_EXISTS (JSON_DOCUMENT, '$?(@.name == "John" && @.address.city == "Georgetown")')

Operationen einer Document-Store-API werden automatisch in äquivalente SQL-Operationen übersetzt. Ein angenehmer Nebeneffekt ist, dass der Query-Optimizer den effizientesten Plan erzeugen kann, zum Beispiel, wenn Indizes oder Materialized Views vorhanden sind oder die Tabelle (Collection) partitioniert, ge-sharded oder in-memory ist.

JSON und Converged Database: Mehr als CRUD-Operationen

SODA-Zugriffe sind einfach und decken das Arbeiten mit Basis-Operationen, auch CRUD-Operationen (Create, Read, Update, Delete) genannt, ab. Es geht dabei um das Ablegen, Lesen, Aktualisieren und Löschen von JSON-Informationen. Mit SQL-Kenntnissen lässt sich allerdings weit mehr mit JSON- Dokumenten bewerkstelligen. Neben einem JSON-Datentyp gibt es dedizierte SQL-Operatoren, die zum Beispiel erlauben, Werte aus JSON-Daten zu extrahieren oder JSON aus relationalen Daten zu erzeugen. Mit der JSON_VALUE oder der Dot-Notation kann einfach auf einzelne Teile im Dokument zugegriffen werden. Folgendes Beispiel extrahiert den Namen aus dem JSON-Dokument.

select m.json_document.name from mynewcollection m;

-- mit JSON_VALUE
 
select json_value(json_document, '$.name')
from mynewcollection;

Damit lässt sich auch ein funktionaler B*Index erzeugen. Sollen hingegen Ad-hoc-Suchen oder Volltextsuchen nach Textfeldern durchgeführt werden, eignet sich der JSON Search Index.

create search index mynewcoll_idx on mynewcollection (JSON_DOCUMENT) for json;

Eine Suche lässt sich dann mit SQL oder mit SODA durchführen.

select * from mynewcollection t
where json_textcontains(t.json_document, '$.address', 'place');

soda get mynewcollection -f {"address" : { "$contains" : "Place" } }

Zusätzlich können Cross-Model-Abfragen – zum Beispiel gemeinsam mit relational gespeicherten Daten aber auch in Kombination mit Spatial usw. – durchgeführt werden. Alle bekannten Konzepte und Techniken der Oracle-Datenbank sind damit nutzbar. Ein Beispiel mit der Oracle Flashback Query zeigt, wie einfach man in die Vergangenheit (z. B. 180 Minuten) zurückgehen kann.

select json_value(json_document, '$.name')
from mynewcollection 
AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '180' MINUTE); 

Für eine ausführlichere Erklärung der SQL/JSON-Operatoren, der verwendeten JSON Path Syntax und weitere Themen wie JSON-Indizierung empfehlen wir den JSON Developer's Guide [2].

Oracle Database API for MongoDB

Nachdem wir einen Überblick über die Eigenschaften JSON-basierter Entwicklung gegeben haben, wollen wir die Oracle Database API for MongoDB genauer vorstellen – dies gestaltet sich erstaunlich unspektakulär: Zurzeit unterstützen die Autonomous JSON Database- und Autonomous Transaction Processing Cloud-Dienste die MongoDB-API, sie muss jedoch durch ACLs freigeschaltet werden (Details im Blog [3]). Danach sind die beiden Standard-MongoDB-Ports 27016 und 27017 geöffnet und die Connect-Strings werden in der Cloud-Konsole angezeigt (Port 27017 unterstützt Load-Balancing und sollte bevorzugt werden).