12.1 bug
SQL> select * from v$version where rownum = 1;
BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> CREATE TABLE test_user (
2 id RAW(16) NOT NULL,
3 loading_date TIMESTAMP(6) WITH TIME ZONE,
4 user_json CLOB CHECK (user_json IS JSON)
5 );
Table created.
SQL>
SQL> INSERT INTO test_user
2 VALUES (
3 SYS_GUID(),
4 SYSTIMESTAMP,
5 '{
6 "user_key":1,
7 "user_name":"Arthur Stygall",
8 "user_type":"End-user",
9 "user_status":"Active",
10 "user_emp_type":"Full-Time",
11 "user_login":"arthurstygall",
12 "user_password":"test1243",
13 "user_address":{
14 "street":"200 Sporting Green",
15 "city":"South San Francisco",
16 "state":"CA",
17 "zipCode":99236,
18 "country":"United States of America"
19 },
20 "user_phone":[
21 {
22 "type":"Office",
23 "number":"823-555-9969"
24 },
25 {
26 "type":"Mobile",
27 "number":"976-555-1234"
28 }
29 ],
30 "user_email":"arthur.stygall@achme.com",
31 "user_create_date":"19-JAN-10",
32 "user_manager_name":"Ron Howard",
33 "user_experience":[
34 {
35 "idx":1,
36 "info":{
37 "Company":"Microsoft",
38 "Role":"Architect",
39 "Description":"Technical architect for Microsoft Azure Cloud"
40 }
41 },
42 {
43 "idx":2,
44 "info":{
45 "Company":"Adobe",
46 "Role":"PMTS",
47 "Description":"Lead developer for Adobe Flash technology"
48 }
49 }
50 ]
51 }'
52 );
1 row created.
SQL>
SQL> create table z_user(
2 user_key varchar2(30),
3 user_name varchar2(30)
4 );
Table created.
SQL>
SQL> create table z_phones(
2 user_key varchar2(30),
3 USER_PHONE_TYPE varchar2(30),
4 USER_PHONE_NUMBER varchar2(30)
5 )
6 ;
Table created.
SQL>
SQL> create table z_experiences(
2 user_key varchar2(30),
3 USER_EXP_IDX varchar2(30),
4 USER_EXP_CMPNY varchar2(30),
5 USER_EXP_ROLE varchar2(30),
6 USER_EXP_DESC varchar2(45)
7 );
Table created.
SQL>
SQL> insert all
2 when rn = 1
3 then into z_user values(user_key, user_name)
4 when up_rn is not null
5 then into z_phones values(user_key, user_phone_type, user_phone_number)
6 when ue_rn is not null
7 then into z_experiences values(user_key, user_exp_idx, user_exp_cmpny, user_exp_role, user_exp_desc)
8 SELECT d.*,
9 row_number() over(partition by user_key order by null) rn
10 FROM test_user,
11 JSON_TABLE(
12 user_json,
13 '$'
14 columns (
15 USER_KEY VARCHAR2(30 CHAR) path '$.user_key',
16 USER_NAME VARCHAR2(30 CHAR) path '$.user_name',
17 NESTED PATH '$.user_phone[*]'
18 columns (
19 up_rn for ordinality,
20 USER_PHONE_TYPE VARCHAR2(24 CHAR) path '$.type',
21 USER_PHONE_NUMBER VARCHAR2(24 CHAR) path '$.number'
22 ),
23 NESTED PATH '$.user_experience[*]'
24 columns (
25 ue_rn for ordinality,
26 USER_EXP_IDX NUMBER(32) path '$.idx',
27 USER_EXP_CMPNY VARCHAR2(32 CHAR) path '$.info.Company',
28 USER_EXP_ROLE VARCHAR2(14 CHAR) path '$.info.Role',
29 USER_EXP_DESC VARCHAR2(32 CHAR) path '$.info.Description'
30 )
31 )
32 ) d;
9 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set lines 200
SQL> select * from z_user;
USER_KEY USER_NAME
------------------------------ ------------------------------
1 Arthur Stygall
1 row selected.
SQL> select * from z_phones;
USER_KEY USER_PHONE_TYPE USER_PHONE_NUMBER
------------------------------ ------------------------------ ------------------------------
1 Office 823-555-9969
1 Mobile 976-555-1234
1
1
4 rows selected.
SQL> select * from z_experiences;
USER_KEY USER_EXP_IDX USER_EXP_CMPNY USER_EXP_ROLE USER_EXP_DESC
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------
1
1
1 1 Microsoft Architect Technical architect for Microsoft Azure Cloud
1 2 Adobe PMTS Lead developer for Adobe Flash technology
4 rows selected.
SQL>
SQL> select * from v$version where rownum = 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> CREATE TABLE test_user (
2 id RAW(16) NOT NULL,
3 loading_date TIMESTAMP(6) WITH TIME ZONE,
4 user_json CLOB CHECK (user_json IS JSON)
5 );
Table created.
SQL>
SQL> INSERT INTO test_user
2 VALUES (
3 SYS_GUID(),
4 SYSTIMESTAMP,
5 '{
6 "user_key":1,
7 "user_name":"Arthur Stygall",
8 "user_type":"End-user",
9 "user_status":"Active",
10 "user_emp_type":"Full-Time",
11 "user_login":"arthurstygall",
12 "user_password":"test1243",
13 "user_address":{
14 "street":"200 Sporting Green",
15 "city":"South San Francisco",
16 "state":"CA",
17 "zipCode":99236,
18 "country":"United States of America"
19 },
20 "user_phone":[
21 {
22 "type":"Office",
23 "number":"823-555-9969"
24 },
25 {
26 "type":"Mobile",
27 "number":"976-555-1234"
28 }
29 ],
30 "user_email":"arthur.stygall@achme.com",
31 "user_create_date":"19-JAN-10",
32 "user_manager_name":"Ron Howard",
33 "user_experience":[
34 {
35 "idx":1,
36 "info":{
37 "Company":"Microsoft",
38 "Role":"Architect",
39 "Description":"Technical architect for Microsoft Azure Cloud"
40 }
41 },
42 {
43 "idx":2,
44 "info":{
45 "Company":"Adobe",
46 "Role":"PMTS",
47 "Description":"Lead developer for Adobe Flash technology"
48 }
49 }
50 ]
51 }'
52 );
1 row created.
SQL>
SQL> create table z_user(
2 user_key varchar2(30),
3 user_name varchar2(30)
4 );
Table created.
SQL>
SQL> create table z_phones(
2 user_key varchar2(30),
3 USER_PHONE_TYPE varchar2(30),
4 USER_PHONE_NUMBER varchar2(30)
5 )
6 ;
Table created.
SQL>
SQL> create table z_experiences(
2 user_key varchar2(30),
3 USER_EXP_IDX varchar2(30),
4 USER_EXP_CMPNY varchar2(30),
5 USER_EXP_ROLE varchar2(30),
6 USER_EXP_DESC varchar2(45)
7 );
Table created.
SQL>
SQL> insert all
2 when rn = 1
3 then into z_user values(user_key, user_name)
4 when up_rn is not null
5 then into z_phones values(user_key, user_phone_type, user_phone_number)
6 when ue_rn is not null
7 then into z_experiences values(user_key, user_exp_idx, user_exp_cmpny, user_exp_role, user_exp_desc)
8 SELECT d.*,
9 row_number() over(partition by user_key order by null) rn
10 FROM test_user,
11 JSON_TABLE(
12 user_json,
13 '$'
14 columns (
15 USER_KEY VARCHAR2(30 CHAR) path '$.user_key',
16 USER_NAME VARCHAR2(30 CHAR) path '$.user_name',
17 NESTED PATH '$.user_phone[*]'
18 columns (
19 up_rn for ordinality,
20 USER_PHONE_TYPE VARCHAR2(24 CHAR) path '$.type',
21 USER_PHONE_NUMBER VARCHAR2(24 CHAR) path '$.number'
22 ),
23 NESTED PATH '$.user_experience[*]'
24 columns (
25 ue_rn for ordinality,
26 USER_EXP_IDX NUMBER(32) path '$.idx',
27 USER_EXP_CMPNY VARCHAR2(32 CHAR) path '$.info.Company',
28 USER_EXP_ROLE VARCHAR2(14 CHAR) path '$.info.Role',
29 USER_EXP_DESC VARCHAR2(32 CHAR) path '$.info.Description'
30 )
31 )
32 ) d;
5 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set lines 200
SQL> select * from z_user;
USER_KEY USER_NAME
------------------------------ ------------------------------
1 Arthur Stygall
1 row selected.
SQL> select * from z_phones;
USER_KEY USER_PHONE_TYPE USER_PHONE_NUMBER
------------------------------ ------------------------------ ------------------------------
1 Office 823-555-9969
1 Mobile 976-555-1234
2 rows selected.
SQL> select * from z_experiences;
USER_KEY USER_EXP_IDX USER_EXP_CMPNY USER_EXP_ROLE USER_EXP_DESC
------------------------------ ------------------------------ ------------------------------ ------------------------------ ---------------------------------------------
1 1 Microsoft Architect Technical architect for Microsoft Azure Cloud
1 2 Adobe PMTS Lead developer for Adobe Flash technology
2 rows selected.
SQL>