with empj as (
select '{
"department": "Accounting",
"employees": [ {
"name": "Shelley,Higgins",
"job": "Accounting Manager"
}, {
"name": "William,Gietz",
"job": "Public Accountant"
}
],
"location": {
"streetAddress": "2004 Charade Rd",
"city": "Seattle"
}
}' j from dual
)
select t.*
from empj e,
json_table (
e.j, '$'
columns (
department path '$.department',
nested path '$.employees[*]'
columns (
name path '$.name',
job path '$.job'
),
street_address path '$.location.streetAddress',
city path '$.location.city'
)
) t| DEPARTMENT | STREET_ADDRESS | CITY | NAME | JOB | Accounting | 2004 Charade Rd | Seattle | Shelley,Higgins | Accounting Manager | Accounting | 2004 Charade Rd | Seattle | William,Gietz | Public Accountant |
|---|
with empj as (
select treat('{
"department": "Accounting",
"employees": [ {
"name": "Shelley,Higgins",
"job": "Accounting Manager"
}, {
"name": "William,Gietz",
"job": "Public Accountant"
}
],
"location": {
"streetAddress": "2004 Charade Rd",
"city": "Seattle"
}
}' as json) j from dual
)
select t.*
from empj e,
json_table (
e.j columns (
department,
nested employees[*] columns ( name, job ),
nested location columns ( streetAddress, city)
)
) t| DEPARTMENT | NAME | JOB | STREETADDRESS | CITY | Accounting | Shelley,Higgins | Accounting Manager | - | - | Accounting | William,Gietz | Public Accountant | - | - | Accounting | - | - | 2004 Charade Rd | Seattle |
|---|
with empj as (
select treat('{
"department": "Accounting",
"employees": [ {
"name": "Shelley,Higgins",
"job": "Accounting Manager"
}, {
"name": "William,Gietz",
"job": "Public Accountant"
}
]
}' as json) j from dual
)
select t.*
from empj e,
json_table (
e.j columns (
department,
first_employee_name path employees[0].name,
employees format json
)
) t| DEPARTMENT | FIRST_EMPLOYEE_NAME | EMPLOYEES | Accounting | Shelley,Higgins | [{"name":"Shelley,Higgins","job":"Accounting Manager"},{"name":"William,Gietz","job":"Public Accountant"}] |
|---|