====JSON in der Oracle Datenbank 19c und 23ai verarbeiten=====
**12c / 18c / 19c / 23ai  **\\

Neben der Eigenschaft als Beschreibungssprache für Datenstrukturen zu dienen, setzt sich [[http://www.json.org/|json]] auch immer mehr bei der Datenspeicherung und -Übertragung durch.


{{ :images:json_frosh.jpg.png?direct&300|Ein Frosch lernt JSON}}

Mit der Oracle Datenbank 12c hat auch Oracle einen JSON Parser in SQL integriert. 



Ein eigener Datentyp existiert dazu erst ab 21c bzw. 23ai , die Daten können in VARCHAR2,CLOB oder BLOB gespeichert werden. 


Es kann ein Constraint auf die Spalte in der Tabelle mit den JSON Daten gelegt werden der überprüft ob das Dokument auch wirklich wohl geformt ist. 


<note tip>Demnächst mehr</note>

----


=== Ein erstes Beispiel ====

Um den Unterschied zwischen traditionellen Datenhandling mit SQL und dem mit JSON aufzuzeigen, legen wir uns zuvor die Inhalte der EMP Tabelle als JSON Strukturen ab.


== Anlegen der Tabelle für die JSON Struktur ==

Eine Tabelle mit einem CLOB Feld für die JSON Daten, diese werden beim Einfügen über eine Constraint auf Gültigkeit geprüft:
<code sql>
create table JSON_EMP (  empno NUMBER(11)
                       , emp_value  CLOB CONSTRAINT emp_valid_json CHECK (emp_value IS JSON));
</code>

Soll ein BLOB Feld zur Datenspeicherung verwendet werden muss bei den Abfragen mit SQL ein Cast auf Json mit "FORMAT JSON" erfolgen!


== Daten aus der EMP Tabelle übertragen ==
Aus dem Emp Daten das Json Dokument pro Eintrag in der Tabelle erzeugen und dann einfügen:
<code sql>
insert into JSON_EMP (empno,emp_value)
select empno
	  ,'{'
		||' "EMPNO":"'||EMPNO||'"'
		||' ,"ENAME":"'||ENAME||'"'
		||' ,"JOB":"'||JOB||'"'
		||' ,"MGR":"'||MGR||'"'
		||' ,"SAL":"'||SAL||'"'
		||' ,"COMM":"'||COMM||'"'
		||' ,"DEPTNO":"'||DEPTNO||'"'
		||'}' 
from emp
/

commit;

</code>

=== In den Daten nun suchen ====

Im einfachsten Fall können die Json Elemente direkt in SQL referenziert werden:

<code sql>
SELECT j.emp_value.ENAME, j.emp_value.DEPTNO FROM JSON_EMP j;
</code>
Auf die GROSS/klein Schreibung achten! Muss genau zu dem JSON Dokument passen!


=== Welchen SQL Funktionen stehen uns zur Verfügung?===

ab 12c Release 1 (12.1.0.2)

  * [[https://docs.oracle.com/database/121/SQLRF/functions093.htm|JSON_VALUE]]
  * [[http://docs.oracle.com/database/121/SQLRF/functions091.htm#SQLRF56718|JSON_QUERY]]
  * [[http://docs.oracle.com/database/121/SQLRF/functions092.htm#SQLRF56973|JSON_TABLE]]


Abfragen:

  * [[http://docs.oracle.com/database/121/SQLRF/conditions010.htm#SQLRF56664|JSON_EXISTS]]
  * [[http://docs.oracle.com/database/121/SQLRF/conditions010.htm#SQLRF56661|IS JSON]]
  * [[http://docs.oracle.com/database/121/SQLRF/conditions010.htm#SQLRF56963|JSON_TEXTCONTAINS]]




----

==== Probleme =====

Leider steckt aber auch hier der Teufel im Detail ...


===ORA-40478: output value too large (maximum: 4000) ===

Wird ein JSON Objekte "zusammen gebaut" mit mehr als 4000 Zeichen muss der Datentyp CLOB verwendet werden.

<code sql>
..
, key 'key1'        is json_serialize(ob.json_rec returning clob)
..
 , key 'details'    is json_arrayagg  ( ed.details_json returning clob)..

...
</code>

D.h. in den Oracle JSON Formaten kann der Rückgabe Datenwert angeben werden

===JSON Output wird escaped==

Wird ein Wert in **json_object** wieder um ein JSON Record übergeben, wird dieser JSON Record als Text betrachtet und excaped!
Mit der Zusäzlichen Angabe "format json" kann das Escapen verhindert werden. 

<code sql>
select json_object ( ....
, KEY 'Contact'        is ob.json_rec format json 
..
returning clob   
)
from table_with_json_records;
  
</code>

<note tip>Achtung in json_object  bezieht sich format json  nur auf den jeweiligen "KEY"! Nicht auf das gesamte Objekt! Wird es also in der letzten Zeile vor "returning clob" geschrieben, fehlen im letzten KEY die """ .-) </note>

----


==== Quellen====

Allgemein
  * [[http://www.json.org/|json]] 

Oracle:
  * https://docs.oracle.com/database/121/ADXDB/json.htm#ADXDB6254
  * https://livesql.oracle.com/apex/livesql/file/tutorial_EDVE861H6UF4Z20EV0RM4DK2G.html
  * https://apex.oracle.com/pls/apex/germancommunities/dbacommunity/tipp/5421/index.html

Veranstaltungen:
  * DOAG 2014 - JSON und die Oracle Database 12c - Carsten Czarski


Demo:

  * https://github.com/oracle/json-in-db