Auswahl  

JSON Umwandlung in realtionales Format mit JSON_TABLE Beispiele 

Oracle
APEX:PL/SQL
RDBMS 19.3:RDBMS 21.1
20.07.22 (MP)
22.07.22
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:
 
A B C
     
1 2 3


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:
Zahl Text Bool Datum1 Datum2 Datum3 Datum4
             
1 aBc true 15.07.2022 00:00:00 15.07.2022 00:00:00 15.07.2022 00:00:00 15.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:
a1 c d
     
a1 c1 1
a1 c1 2


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
A B
1 2
3 4
5 6


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
A B
1 2
3 4
5 6


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
Vorname Nachname
   
Marco Patzwahl
Hansi Wurst
Karin Sorglos


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
ID Var2 SUB_ID SUB_VAL
123 abc 1 a
123 abc 2 b
       


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:
 
A B
   
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:
DEPTNO DNAME LOC HREF
       
10 ACCOUNTING NEW YORK http://127.0.0.1:8080/ords/scott/dept/10
20 RESEARCH DALAS http://127.0.0.1:8080/ords/scott/dept/20
30 SALES CHICAGO http://127.0.0.1:8080/ords/scott/dept/30
40 OPERATIONS BOSTON http://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;
DEPTNO DNAME LOC HREF HASMORE OFFSET COUNT HREF2 HFREF3
                 
40 OPERATIONS BOSTON http://127.0.0.1:8080/ords/scott/dept/40 false 0 4 http://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
        }
      ]
    }
  ]
}' as txt from dual)
select ord,max(v0) as v0, max(v1) as v1,max(v2) as v2,max(v3) as v3
FROM j_son,
JSON_TABLE(j_son.txt, '$.items[*].sub'
  COLUMNS (ord for ordinality,
  nested path '$[*]' columns (
    v0 path '$?(@.a == 4).b',
    v1 path '$?(@.a == 7).b',
    v2 path '$?(@.a == 10).b',
    v3 path '$?(@.a == 13).b'
    )
  )
)         
AS "JT"
GROUP by ord;
Ergebnis:
ORD V0 V1 V2 V3
         
1 5 8    
2     11 14



Tipps & Tricks:
Wenn Sie Problememit der Zerlegung des JSON Ausdrucks haben, testen Sie zuerst, ob der JSON Ausdruck valide ist.
z.B hier: https://jsonformatter.curiousconcept.com/#