By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT
jt.*
FROM
JSON_TABLE(
'{
"XX_data":[
{
"employee_id": "E1",
"full_name": "E1 Admin",
"past_work": "N/A"
},
{
"employee_id": "E2",
"full_name": "E2 Admin",
"past_work": [
{"company": "E2 PW1 C", "title": "E2 PW1 T"},
{"company": "E2 PW2 C", "title": "E2 PW2 T"},
]
},
]
}',
'$.XX_data[*]'
COLUMNS (
employee_id VARCHAR2(100) PATH '$.employee_id',
full_name VARCHAR2(100) PATH '$.full_name',
past_work VARCHAR2(100) PATH '$.past_work',
NESTED PATH '$.past_work[*]'
COLUMNS (
past_work_company VARCHAR2(100) PATH '$.company',
past_work_title VARCHAR2(100) PATH '$.title'
)
)
)
AS jt
| EMPLOYEE_ID | FULL_NAME | PAST_WORK | PAST_WORK_COMPANY | PAST_WORK_TITLE |
|---|---|---|---|---|
| E1 | E1 Admin | N/A | null | null |
| E2 | E2 Admin | null | E2 PW1 C | E2 PW1 T |
| E2 | E2 Admin | null | E2 PW2 C | E2 PW2 T |