Auswahl  

JSON Daten extrahieren 

Oracle
APEX:PL/SQL
RDBMS 18.3:RDBMS 19.3:RDBMS 21.1
19.05.22 (MP)
19.05.22
JSON, Oracle

Passende Schulungen zum Thema

In unserem heutigen Beitrag wollen wir uns mit dem Thema beschäftigen, wie man aus einem JSON String wieder die Daten extrahieren kann.
Diesen Anwendungsfall hatte ich die letzten 24 Monat sehr häufig, deswegen wurde es mal wieder Zeit sich mit dem Thema JSON in Oracle zu beschäftigen.

Wir fangen mit einer kleinen Test-Tabelle basiernd auf den Emp und Dept Daten des Benutzers SCOTT an:
Die Besonderheit / Schwierigkeit besteht darin, dass die Mitarbeiter in einem Array eingetragen werden (["CLARK","KING","MILLER"]
 
CREATE TABLE dept_json   
( json_text CLOB,   
    CONSTRAINT check_json CHECK ( json_text IS JSON ) );

INSERT INTO dept_json   
VALUES ('{ "DEPT" : {
                       "DEPTNO" : 10,
                       "DNAME" : "ACCOUNTING",   
                       "LOC" : "NEW YORK",  
                       "EMPS"  : ["CLARK","KING","MILLER"] }}');

INSERT INTO dept_json   
VALUES ('{"DEPT" : {
                       "DEPTNO" : 20,
                       "DNAME" : "RESEARCH",   
                       "LOC" : "DALLAS",  
                       "EMPS"  : ["SMITH","JONES","SCOTT","ADAMS","FORD"] }}');


INSERT INTO dept_json   
VALUES ('{"DEPT" : {
                       "DEPTNO" : 30,
                       "DNAME" : "SALES",   
                       "LOC" : "CHICAGO",  
                       "EMPS"  : ["ALLEN","BLAKE","JAMES","MARTIN","TURNER","WARD"] }}');

Als Bonustrack legen wir einen Oracle Text-Index auf die JSON Spalte:

CREATE INDEX dept_json_index   
  ON dept_json(json_text)   
  INDEXTYPE IS CTXSYS.CONTEXT   
  PARAMETERS ('SECTION GROUP CTXSYS.JSON_SECTION_GROUP SYNC (ON COMMIT)');

Wir können dann mit der Funktion JSON_TEXTCONTAINS im JSON String suchen.
SELECT json_text   
  FROM dept_json   
 WHERE JSON_TEXTCONTAINS(json_text, '$', 'KING');

Das Ergebnis sieht dann so aus:

{ "DEPT" : {
                       "DEPTNO" : 10,
                       "DNAME" : "ACCOUNTING",   
                       "LOC" : "NEW YORK",  
                       "EMPS"  : ["CLARK","KING","MILLER"] }}

Wenn man die Daten der JSON Tabelle wieder extrahieren (und weiterverarbeiten) möchte:

SELECT deptno, dname, loc, linenum, arrayval
from dept_json,json_table(json_text, '$.DEPT[*]'
columns ( arraynum for ordinality, DEPTNO,DNAME,LOC,
    nested path '$.EMPS[*]'
        columns ( linenum for ordinality,nested path '$[*]'
                 columns( arrayval varchar2 path '$')
  ) ));

Das Ergebnis sieht dann so aus:
 
DEPTNO DNAME LOC LINENUM ARRAYVAL
10 ACCOUNTING NEW YORK 1 CLARK
10 ACCOUNTING NEW YORK 2 KING
10 ACCOUNTING NEW YORK 3 MILLER
20 RESEARCH DALLAS 1 SMITH
20 RESEARCH DALLAS 2 JONES
20 RESEARCH DALLAS 3 SCOTT
20 RESEARCH DALLAS 4 ADAMS
20 RESEARCH DALLAS 5 FORD
30 SALES CHICAGO 1 ALLEN
30 SALES CHICAGO 2 BLAKE
30 SALES CHICAGO 3 JAMES
30 SALES CHICAGO 4 MARTIN
30 SALES CHICAGO 5 TURNER
30 SALES CHICAGO 6 WARD

Wenn man mal einen unbekannten JSON String auswerten möchte stellt Oracle dafür auch ein Package zur Verfügung:
SELECT json_dataguide(json_text, DBMS_JSON.FORMAT_HIERARCHICAL, DBMS_JSON.PRETTY)
FROM dept_json;

Die Ausgabe sieht dann so aus:
{
  "type" : "object",
  "o:length" : 128,
  "properties" :
  {
    "DEPT" :
    {
      "type" : "object",
      "o:length" : 128,
      "o:preferred_column_name" : "DEPT",
      "properties" :
      {
        "LOC" :
        {
          "type" : "string",
          "o:length" : 8,
          "o:preferred_column_name" : "LOC"
        },
        "EMPS" :
        {
          "type" : "array",
          "o:length" : 64,
          "o:preferred_column_name" : "EMPS",
          "items" :
          {
            "type" : "string",
            "o:length" : 8,
            "o:preferred_column_name" : "scalar_string"
          }
        },
        "DNAME" :
        {
          "type" : "string",
          "o:length" : 16,
          "o:preferred_column_name" : "DNAME"
        },
        "DEPTNO" :
        {
          "type" : "number",
          "o:length" : 2,
          "o:preferred_column_name" : "DEPTNO"
        }
      }
    }
  }
}

Und nun viel Spaß mit der Auswertung von Daten aus JSON-Ausdrücken. Bei uns wird das Thema ausführlich behandelt in den Kursen Oracle PL/SQL II, Oracle REST und Oracle Dev New Features 12c - 21c

Weitere Interessante Kurs-Tipps zum Thema

Oracle JSON Date Datentyp Problem
Oracle und JSON
Oracle APEX 20.2 Installation

Besuchen Sie uns doch bei einer unsere über 50 Oracle Schulungen in München - Unterhaching, oder verfolgen Sie die Training per Videostreaming bequem vom Home Office oder Büro aus.