Skip to Main Content

 

Auswahl  

JSON Umwandlung in realtionales Format mit JSON_TABLE Beispiele 

Oracle
APEX:PL/SQL
RDBMS 19.3
20.07.22 (MP)
05.07.23(MP)
JSON, Array

Passende Schulungen zum Thema

In vielen unserer Kurse ist JSON ein Thema (z.B. im APEX oder ORDS oder PL/SQL II Kurs). Da die Syntax gewöhnungsbedürftig ist, haben wir mal ein paar Beispiele generiert um die syntax besser zu verstehen.
Ausgangslage ist ein JSON String, der in ein relationales Modell überführt werden soll.

Übersicht der Beispiele:
Beispiel 1, mit 3 Werte
Beispiel 2, Verschiedene Datentypen in JSON
Beispiel 3, mit Geschachtelten Arrays
Beispiel 4, mit 3 Zeilen mit 2 Feldern
Beispiel 5, mit 3 Zeilen mit 2 Feldern und einem Root Eintrag (var)
Beispiel 6, mit 2 Feldern und 3 Zeilen
Beispiel 7, mit Sub-Sub Feldern
Beispiel 8, nur einen Wert extrahieren
Beispiel 9, Wieviele Elemente hat das Array?
Beispiel 10, Sub-Arrays (Darstellungsvariante 1)
Beispiel 11, Sub-Arrays (Darstellungsvariante 2)   
Beispiel 12, Tabelle die von Oracle mit Auto-Rest zurückkommt zerlegen
Beispiel 13: JSON String gefiltert

Beispiel 1, mit 3 Werte

WITH j_son as (SELECT '
{"a":1,
 "b":2,
 "c":3
}' as txt from dual)
SELECT a,b,c
FROM j_son,  
     JSON_TABLE(j_son.txt, '$'
     COLUMNS ( a,b,c )
          ) jt;


Ergebnis:
 

ABC
   
123



Beispiel 2, Verschiedene Datentypen in JSON:

WITH j_son as (SELECT '
{"Zahl":1, "Text": "aBc", "Bool":TRUE,
"Datum1":"2022-07-15", "Datum2":"2019-07-15T14:11:27",
"Datum3":"2022-07-15T14:11:27Z", "Datum4":"2022-07-15T14:11:27+00:00"
}' as txt from dual)
SELECT Zahl,Text,Bool,Datum1,Datum2,Datum3,Datum4
FROM j_son,  JSON_TABLE(j_son.txt, '$' COLUMNS (
       Zahl NUMBER PATH '$."Zahl"',
       Text VARCHAR2(2000) PATH '$."Text"',
       Bool VARCHAR2(10) PATH '$."Bool"',
       Datum1 DATE PATH '$."Datum1"',
       Datum2 DATE PATH '$."Datum2"',
       Datum3 DATE PATH '$."Datum3"',
       Datum4 DATE PATH '$."Datum4"'
          )
     ) jt;


Ergebnis:

ZahlTextBoolDatum1Datum2Datum3Datum4
       
1aBctrue15.07.2022 00:00:0015.07.2022 00:00:0015.07.2022 00:00:0015.07.2022 00:00:00

Hinweis: Die Stunden / Minuten / Sekunden verschwinden hier. Alternative: Datum als Text parsen und mittels to_date in Datum umwandeln.

Beispiel 3, mit Geschachtelten Arrays

WITH j_son as (SELECT '
{"a":"a1",
 "b": [
        {"c": "c1",
         "d": [1,2]}
      ]
}' as txt from dual)
SELECT j_son.txt, "a","c","d"
FROM j_son,  
     JSON_TABLE(j_son.txt, '$' COLUMNS (
       "a" VARCHAR2(5) PATH '$.a',
       NESTED PATH '$.b[*]' COLUMNS (
          "c" VARCHAR2(5) PATH '$.c',
          NESTED PATH '$.d[*]' COLUMNS (
             "d" NUMBER PATH '$[0]'
          )
       )
     )) jt;

    
Ergebnis:

a1cd
   
a1c11
a1c12



Beispiel 4, mit 3 Zeilen mit 2 Feldern

WITH j_son as (SELECT '
[{"A":1,"B":2},
{ "A":3,"B":4},
{ "A":5,"B":6}]' txt from dual)
SELECT A,B
FROM j_son,json_table(j_son.txt,'$[*]'
columns (A,B));

 Ergebnis

AB
12
34
56



Beispiel 5, mit 3 Zeilen mit 2 Feldern und einem Root Eintrag (var)

WITH j_son as (select '
{var:[{"A":1,"B":2},
{ "A":3,"B":4},
{ "A":5,"B":6}]}' txt from dual)
select A,B
FROM j_son,json_table(j_son.txt,'$.var[*]' columns (A,B));

  Ergebnis

AB
12
34
56



Beispiel 6, mit 2 Feldern und 3 Zeilen

WITH j_son as (select '[
{"Vorname":"Marco","Nachname":"Patzwahl"},
{"Vorname":"Hansi","Nachname":"Wurst"},
{"Vorname":"Karin","Nachname":"Sorglos"}
]' txt from dual)
select vorname,nachname
FROM j_son,json_table(j_son.txt,'$[*]'
columns (Vorname,Nachname));

  Ergebnis

VornameNachname
  
MarcoPatzwahl
HansiWurst
KarinSorglos



Beispiel 7, mit Sub-Sub Feldern

SELECT id, var1, sub_id, sub_val
FROM JSON_TABLE (
        to_clob('{ id: 123, var1: "abc",
        subvalues : [
            { id: 1, value: "a", },
            { id: 2, value: "b" } ]} '),
        '$' COLUMNS (
            id NUMBER PATH '$.id',
            var1 VARCHAR PATH '$.var1',
            NESTED PATH '$.subvalues[*]'
            COLUMNS ( sub_id NUMBER PATH '$.id',
            sub_val VARCHAR2(4000) PATH '$.value'
            ) ) );


oder

WITH j_son as (select '{ id: 123, var1: "abc",
        subvalues : [
            { id: 1, value: "a", },
            { id: 2, value: "b" }
            ]}' as txt from dual)
SELECT id, var1, sub_id, sub_val
FROM j_son,JSON_TABLE (j_son.txt,
        '$' COLUMNS (
            id NUMBER PATH '$.id',
            var1 VARCHAR PATH '$.var1',
            NESTED PATH '$.subvalues[*]'
            COLUMNS ( sub_id NUMBER PATH '$.id',
            sub_val VARCHAR2(4000) PATH '$.value'
            ) ) );

  Ergebnis

IDVar2SUB_IDSUB_VAL
123abc1a
123abc2b
    



Beispiel 8, nur einen Wert extrahieren

SELECT json_value ('{"emp":{ "name":"Marco","city":"Bern" } } ','$.emp.name') as name
FROM dual;


Beispiel 9, Wieviele Elemente hat das Array?

SELECT json_value('[1, 2, 3, 4 ]','$.size()') FROM dual;

=>4
oder

SELECT json_query('[1,[2,2,2],3]','$[*].size()' WITH ARRAY WRAPPER)  FROM dual;

=>[1,3,1]

Beispiel 10: Sub-Arrays (Darstellungsvariante 1)

WITH j_son as (select '
{var:[{"A":[1,2],"B":[3,4]},
{ "A":[5,6],"B":[7,8]},
]}' txt from dual)
select A,B
FROM j_son,json_table(j_son.txt,
'$.var' columns (
    nested path '$.A[*]' columns (A number path '$[0]'),
    nested path '$.B[*]' columns (B number path '$[0]') ));

    
Ergebnis:
 

AB
  
1 
2 
 3
 4
5 
6 
 7
 8


   
Beispiel 11: Sub-Arrays (Darstellungsvariante 2   

WITH j_son as (select '
{var:[{"A":[1,2],"B":[3,4]},
{ "A":[5,6],"B":[7,8]},
]}' txt from dual)
select val
FROM j_son,json_table(j_son.txt,
'$.var' columns (
    nested path '$.*[*]' columns (val number path '$[0]' )));

    

VAL
 
1
2
3
4
5
6
7
8


Beispiel 12, Tabelle die von Oracle mit Auto-Rest zurückkommt zerlegen

WITH j_son as (SELECT '
{"items":[
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK",
"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/scott/dept/10"}]},
{"deptno":20,"dname":"RESEARCH","loc":"DALLAS",
"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/scott/dept/20"}]},
{"deptno":30,"dname":"SALES","loc":"CHICAGO",
"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/scott/dept/30"}]},
{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON",
"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/scott/dept/40"}]}],
"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/scott/dept/"},
{"rel":"edit","href":"http://127.0.0.1:8080/ords/scott/dept/"},
{"rel":"describedby","href":"http://127.0.0.1:8080/ords/scott/metadata-catalog/dept/"},
{"rel":"first","href":"http://127.0.0.1:8080/ords/scott/dept/"}]}' as txt from dual)
SELECT deptno,dname,loc,href
FROM  
     j_son,JSON_TABLE(j_son.txt, '$.items[*]'
     COLUMNS ( deptno,dname,loc,
     nested path '$.links[*]' columns (href),
     limit,offset,count)
          ) jt;

Ergebnis:

DEPTNODNAMELOCHREF
    
10ACCOUNTINGNEW YORKhttp://127.0.0.1:8080/ords/scott/dept/10
20RESEARCHDALAShttp://127.0.0.1:8080/ords/scott/dept/20
30SALESCHICAGOhttp://127.0.0.1:8080/ords/scott/dept/30
40OPERATIONSBOSTONhttp://127.0.0.1:8080/ords/scott/dept/40

oder ausführlicher (aber nur für eine Ergebnis-Zeile zur Vereinfachung):


WITH j_son as (SELECT '
{"items":[
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK",
"links":[{"rel":"self","href":"http://127.0.0.1:8080/ords/scott/dept/40"}]}],
"hasMore":false,"limit":25,"offset":0,"count":4,"links":[{"rel":"self",
"href":"http://127.0.0.1:8080/ords/scott/dept1/"},
{"rel":"edit","href":"http://127.0.0.1:8080/ords/scott/dept2/"},
{"rel":"describedby","href":"http://127.0.0.1:8080/ords/scott/metadata-catalog/dept/"},
{"rel":"first","href":"http://127.0.0.1:8080/ords/scott/dept3/"}]}' as txt from dual)
SELECT deptno,dname,loc,href,hasMore,offset,count,href2,href3
FROM  j_son,
     JSON_TABLE(j_son.txt, '$.items[*]'
     COLUMNS ( deptno,dname,loc,
     nested path '$.links[*]' columns (href)) ),
     JSON_TABLE(j_son.txt, '$'
     COLUMNS ( hasMore,limit,offset,count,
     nested path '$.links[0]' columns (href2 varchar2 path '$.href')) ),
     JSON_TABLE(j_son.txt, '$'
     COLUMNS (
     nested path '$.links[1]' columns (href3 varchar2 path '$.href')) )jt;
DEPTNODNAMELOCHREFHASMOREOFFSETCOUNTHREF2HFREF3
         
40OPERATIONSBOSTONhttp://127.0.0.1:8080/ords/scott/dept/40false04http://127.0.0.1:8080/ords/scott/dept1/http://127.0.0.1:8080/ords/scott/dept2/

Beispiel 13: JSON String gefiltert (finde die Werte für B, wenn a=4 , 7, 10 oder 13 ist)

WITH j_son AS (
select '{
  "items": [
    {
      "sub": [
        {
          "a": 1,
          "b": 2,
          "c": 3
        },
        {
          "a": 4,
          "b": 5,
          "c": 6
        },
        {
          "a": 7,
          "b": 8,
          "c": 9
        }
      ]
    },
    {
      "sub": [
        {
          "a": 10,
          "b": 11,
          "c": 12
        },
        {
          "a": 13,
          "b": 14,
          "c": 15
        }
      ]
    }
  ]

Weitere Interessante Kurs-Tipps zum Thema

JSON Daten extrahieren
Oracle und JSON
Oracle JSON Date Datentyp Problem
APEX 21.1 für Oracle XE 18c installieren
20 CREATE TABLE Beispiele für Oracle (Version 10.2 bis 21c)

Kurse zum Thema

Oracle PL/SQL MS 250
PL/SQL II Fortschrittskurs MS 300
PL/SQL Tuning MS 270
Oracle PL/SQL Packages MS 350
Oracle PL/SQL Kompakt MS 260
Oracle Application Express MS 500
Application Express (APEX) Fortschrittskurs MS 550
APEX Security MS 501
APEX Kompaktkurs MS 510
Einsatz von Oracle APEX im Internet MS 507

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.

2024.04.23