Tipp Index

Oracle Tipp Text

JSON Umwandlung in realtionales Format mit JSON_TABLE Beispiele

Bereich:APEX:PL/SQL, Version: ab RDBMS 19.3, Letzte Überarbeitung: 22.07.2022

Keywords:JSON, Array

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
        },
        {
   &nb

1 - 1

Search Results

JSON Umwandlung in realtionales Format mit JSON_TABLE Beispiele

Bereich:APEX:PL/SQL, Version: ab RDBMS 19.3, Letzte Überarbeitung: 22.07.2022

Keywords:JSON, Array