Daten in JSON Format: wie geht das?

Wie kann man Daten (gespeichert in der Oracle Datenbank) in JSON Format umwandeln? Mittlerweile gibt es dazu in der Oracle Datenbank eine Reihe von Möglichkeiten.

Je nachdem wie man sich die Ausgabe vorstellt, kann man beispielsweise in einer SELECT Anweisung die Funktionen LISTAGG, CONCAT oder UNPIVOT verwenden. Bei einer komplexen JSON Ausgabe bieten sich allerdings die speziellen SQL/JSON Funktionen an, die seit Oracle 12.2 in der Datenbank enthalten sind und genau für solche Anforderungen entwickelt worden sind.

Im ersten Beispiel werden die Werte aus den Spalteninhalten mithilfe der Funktion JSON_OBJECT generiert. Die Ausgabe wird in Form eines JSON Objekts augegeben. Wie immer werden die Tabellen EMP und DEPT verwendet.
Hinweis: Wir haben JSON_SERIALIZE mit PRETTY verwendet, um eine besser lesbare Textdarstellung des JSON-Dokuments zu erhalten.

SQL> select JSON_SERIALIZE(JSON_OBJECT(e.*, d.*) pretty) ausgabe
     from scott.dept d join scott.emp e on d.deptno=e.deptno; 
AUSGABE
--------------------------------------------------------------
{
  "EMPNO" : 7839,
  "ENAME" : "KING",
  "JOB" : "PRESIDENT",
  "MGR" : null,
  "HIREDATE" : "1981-11-17T00:00:00",
  "SAL" : 5000,
  "COMM" : null,
  "DEPTNO" : 10,
  "DEPTNO" : 10,
  "DNAME" : "ACCOUNTING",
  "LOC" : "NEW YORK"
} 
{
  "EMPNO" : 7698,
  "ENAME" : "BLAKE",
  "JOB" : "MANAGER",
  "MGR" : 7839,
  "HIREDATE" : "1981-05-01T00:00:00",
...

Natürlich kann man das Ganze auch genauer spezifizieren. In folgendem Beispiel soll nur die EMPNO als EMP_ID und der zugehörige Department Name ausgegeben werden.

SQL> select JSON_OBJECT('emp_id' is e.empno, 'department' is d.dname) ausgabe
     from scott.dept d join scott.emp e on d.deptno=e.deptno; 

AUSGABE
-----------------------------------------------------------------------------
{"emp_id":7782,"department":"ACCOUNTING"}
{"emp_id":7839,"department":"ACCOUNTING"}
{"emp_id":7934,"department":"ACCOUNTING"}
{"emp_id":7566,"department":"RESEARCH"}
{"emp_id":7902,"department":"RESEARCH"}
{"emp_id":7876,"department":"RESEARCH"}
{"emp_id":7369,"department":"RESEARCH"}
{"emp_id":7788,"department":"RESEARCH"}
{"emp_id":7521,"department":"SALES"}
{"emp_id":7844,"department":"SALES"}
{"emp_id":7499,"department":"SALES"}
{"emp_id":7900,"department":"SALES"}
{"emp_id":7698,"department":"SALES"}
{"emp_id":7654,"department":"SALES"}
14 rows selected.

Die vollständige Syntax zu JSON_OBJECT findet sich hier.

Im nächsten Beispiel sollen die Angestellten aus der EMP Tabelle als JSON Aggregat zur Verfügung gestellt werden. Hierzu wird die Funktion JSON_OBJECTAGG verwendet.

 SQL> select d.deptno deptno, JSON_OBJECTAGG('emp_name' is ename) ename_per_deptno
      from scott.dept d join scott.emp e on d.deptno=e.deptno group by d.deptno; 

DEPTNO
------
ENAME_PER_DEPTNO
---------------------------------------------------------------------------------------------
    10
{"emp_name":"CLARK","emp_name":"MILLER","emp_name":"KING"} 
    20
{"emp_name":"SMITH","emp_name":"FORD","emp_name":"ADAMS","emp_name":"SCOTT","emp_name":"JONES"}
    30
{"emp_name":"ALLEN","emp_name":"JAMES","emp_name":"TURNER","emp_name":"BLAKE","emp_name":"MARTIN","emp_name"
:"WARD"}

Der Link zur detaillierten Syntax findet sich hier.

Im letzten Beispiel wird mithilfe von JSON_ARRAYAGG ein JSON Array erzeugt. Pro Abteilung sollen die entsprechenden Angestellten in einem JSON Array ausgegeben werden.

SQL> set pagesize 1000
SQL> select JSON_OBJECT('dname' is d1.dname,
                        'empinfo' is
                        (select JSON_ARRAYAGG(json_object('ename' is ename,'sal' is sal,                
                         'comm' is comm absent on null) absent on null)
     from scott.dept d join scott.emp e on d.deptno=e.deptno) absent on null) as ct1
     from scott.dept d1; 

CT1
------------------------------------------------------------------------------------------------------------
{"dname":"ACCOUNTING","empinfo":[{"ename":"SMITH","sal":800},{"ename":"ALLEN","sal":1600,"comm":300},{"ename
":"WARD","sal":1250,"comm":500},{"ename":"JONES","sal":2975},{"ename":"MARTIN","sal":1250,"comm":1400},{"ena
me":"BLAKE","sal":2850},{"ename":"CLARK","sal":2450},{"ename":"SCOTT","sal":3000},{"ename":"KING","sal":5000
},{"ename":"TURNER","sal":1500,"comm":0},{"ename":"ADAMS","sal":1100},{"ename":"JAMES","sal":950},{"ename":"
FORD","sal":3000},{"ename":"MILLER","sal":1300}]} 
{"dname":"RESEARCH","empinfo":[{"ename":"SMITH","sal":800},{"ename":"ALLEN","sal":1600,"comm":300},{"ename":
"WARD","sal":1250,"comm":500},{"ename":"JONES","sal":2975},{"ename":"MARTIN","sal":1250,"comm":1400},{"ename
":"BLAKE","sal":2850},{"ename":"CLARK","sal":2450},{"ename":"SCOTT","sal":3000},{"ename":"KING","sal":5000},
{"ename":"TURNER","sal":1500,"comm":0},{"ename":"ADAMS","sal":1100},{"ename":"JAMES","sal":950},{"ename":"FO
RD","sal":3000},{"ename":"MILLER","sal":1300}]} ...

Der Link zur detaillierten JSON_ARRAYAGG Syntax findet sich hier. Hat man keine speziellen Ansprüche bzgl der Ausgabe, kann man auch einfach das Werkzeug Oracle SQL Developer Command Line (kurz SQLcl) verwenden. SQLcl vereinigt dabei die Vorteile eines Linemode Werkzeugs mit den Features von SQL Developer und kann genau wie der SQL Developer separat von OTN geladen und installiert werden. Folgendes Beispiel zeigt die Syntax des SQLcl Kommandos SET SQLFORMAT, mit dem man eine JSON Ausgabe erzielen kann.

ulrike_sch@cloudshell:~ (eu-frankfurt-1)$ sql /nolog

SQLcl: Release 24.4 Production on Sun Mar 23 15:32:55 2025
Copyright (c) 1982, 2025, Oracle.  All rights reserved.

SQL> set cloudconfig /home/ulrike_sch/wallet19/<walletdatei>.zip
SQL> connect scott/<password>@<servicename>
Connected.

SQL> help set sqlformat
SET SQLFORMAT
  SET SQLFORMAT { default,csv,text,html,xml,json,fixed,insert,loader,delimited,ansiconsole}   
   default        : SQL*PLUS style formatting 
   csv            : comma separated and string enclosed with " 
   text           : tab separated with customizable left and right enclosures (default enclosure is ")
                    set sqlformat text [left enclosure] [right enclosure] 
                    Example:  
                    set sqlformat text < >  
                       7369    <SMITH> <CLERK> 7902    17-DEC-80   800 20,5555555555554444     html           : html tabular format 
   xml            : xml format of /results/rows/column/* 
   json           : json format matching ORDS Collection Format 
   json-formatted : json format matching ORDS Collection Format and pretty printed 
...



SQL> set sqlformat JSON
SQL> select * from emp;

{"results":[{"columns":[{"name":"EMPNO","type":"NUMBER"},{"name":"ENAME","type":"VARCHAR2"},{"name":"JOB","type":"VARCHAR2"},{"name":"MGR","type":"NUMBER"},{"name":"HIREDATE","type":"DATE"},{"name":"SAL","type":"NUMBER"},{"name":"COMM","type":"NUMBER"},{"name":"DEPTNO","type":"NUMBER"}],"items":
[
{"empno":7839,"ename":"KING","job":"PRESIDENT","hiredate":"17-NOV-81","sal":5000,"deptno":10}
,{"empno":7698,"ename":"BLAKE","job":"MANAGER","mgr":7839,"hiredate":"01-MAY-81","sal":2850,"deptno":30}
,{"empno":7782,"ename":"CLARK","job":"MANAGER","mgr":7839,"hiredate":"09-JUN-81","sal":2450,"deptno":10}
,{"empno":7566,"ename":"JONES","job":"MANAGER","mgr":7839,"hiredate":"02-APR-81","sal":2975,"deptno":20}
,{"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"19-APR-87","sal":3000,"deptno":20}
,{"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"03-DEC-81","sal":3000,"deptno":20}
,{"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"17-DEC-80","sal":800,"deptno":20}
,{"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"20-FEB-81","sal":1600,"comm":300,"deptno":30}
,{"empno":7521,"ename":"WARD","job":"SALESMAN","mgr":7698,"hiredate":"22-FEB-81","sal":1250,"comm":500,"deptno":30}
,{"empno":7654,"ename":"MARTIN","job":"SALESMAN","mgr":7698,"hiredate":"28-SEP-81","sal":1250,"comm":1400,"deptno":30}
,{"empno":7844,"ename":"TURNER","job":"SALESMAN","mgr":7698,"hiredate":"08-SEP-81","sal":1500,"comm":0,"deptno":30}
,{"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"23-MAY-87","sal":1100,"deptno":20}
,{"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"03-DEC-81","sal":950,"deptno":30}
,{"empno":7934,"ename":"MILLER","job":"CLERK","mgr":7782,"hiredate":"23-JAN-82","sal":1300,"deptno":10}
]}]}
14 rows selected.

Liegt das SQL im XML Format vor, gibt es auch seit einiger Zeit die Funktion XMLTOJSON, die XML in JSON umwandeln kann.

SQL> select XMLTOJSON(XMLType('<person><name>Martin</name><id>1234</id></person>')) ausgabe 
     from dual; 

AUSGABE
--------------------------------------------------------------------------------
{"person":{"id":1234,"name":"Martin"}}

Oder auch mit gespeicherten XML Dokumenten …

SQL> select XMLTOJSON(xmldoc) ausgabe 
     from scott.x_tab where rownum=1;

AUSGABE
------------------------------------------------------------------------------------------------------------
{"Abteilungen":{"Abteilung":[{"@nr":1,"Deptname":"Sales Consulting","Mitarbeiter":[{"Addresse":{"City":"Berl
in","Street":"Riesstr"},"Age":30,"Name":"Ulrike Mayer"},{"Addresse":{"City":"Berlin","Street":"Riesstr"},"Ag
e":40,"Name":"Martin Scholz"}]},{"@nr":2,"Deptname":"Consulting","Mitarbeiter":[{"Addresse":{"City":"Muenche
n","Street":"Riesstr"},"Age":30,"Name":"Martina Schwinn"},{"Addresse":{"City":"Muenchen","Street":"Riesstr"}
,"Age":25,"Name":"Peter Schulte"}]},{"@nr":3,"Deptname":"Support","Mitarbeiter":{"Addresse":{"City":"Berlin"
,"Street":"Riesstr"},"Age":22,"Name":"Peter schulte"}}]}}

Übrigens der umgekehrte Weg kann mit JSONTOXML errreicht werden.

Weitere Informationen zu den SQL/JSON Funktionen kann man im SQL Language Guide oder im JSON Developer’s Guide nachlesen.

Daten in JSON Format: wie geht das?

Ulrike Schwinn

Distinguished Data Management Expert

DBMS_XMLSCHEMA_UTIL for XML schema validation

Oracle Database Monthly News - March 2025 - Quick Links

Daten in JSON Format: wie geht das?

Authors

Ulrike Schwinn

Distinguished Data Management Expert

DBMS_XMLSCHEMA_UTIL for XML schema validation

Oracle Database Monthly News - March 2025 - Quick Links