Thanks for the question, Ram.
Asked: September 29, 2016 - 7:45 am UTC
Last updated: September 29, 2016 - 1:09 pm UTC
Version: 12.1.0.2.0
Viewed 1000+ times
You Asked
Hi AskTom,
First of all thanks for your contribution to oracle Community! :-)
I have this problem where i want to parse a json file stored in clob column and after parsing i have to store it in separate table , i am able to parse this through sql query uisng dot notation but to make solution more robust i want to do it through procedure . Can you please suggest what is the best way to do it .
create table json_test
(json_doc clob);
insert into json_test values ('{
"department": {
"department_number": 10,
"department_name": "ACCOUNTING",
"employees": [
{
"employee_number": 7782,
"employee_name": "CLARK"
},
{
"employee_number": 7839,
"employee_name": "KING"
},
{
"employee_number": 7934,
"employee_name": "MILLER"
}
]
},
"metadata": {
"published_date": "04-APR-2016",
"publisher": "oracle-base.com"
}
}');
i want to insert 3 different rows in my employee table for corresponding json file.
Thanks in Advance!
and Connor said...
You can use the JSON_TABLE syntax for this
SQL> create table json_test
2 (json_doc clob);
Table created.
SQL>
SQL>
SQL>
SQL> insert into json_test values ('{
2 "department": {
3 "department_number": 10,
4 "department_name": "ACCOUNTING",
5 "employees": [
6 {
7 "employee_number": 7782,
8 "employee_name": "CLARK"
9 },
10 {
11 "employee_number": 7839,
12 "employee_name": "KING"
13 },
14 {
15 "employee_number": 7934,
16 "employee_name": "MILLER"
17 }
18 ]
19 },
20 "metadata": {
21 "published_date": "04-APR-2016",
22 "publisher": "oracle-base.com"
23 }
24 }');
1 row created.
SQL> select dname, emp_name, emp_num
2 from json_test,
3 json_table(json_doc,'$.department'
4 columns (
5 dname varchar2(30) path '$.department_name'
6 ,nested path '$.employees[*]'
7 columns (
8 emp_name varchar2(20) path '$.employee_name',
9 emp_num number path '$.employee_number'
10 )
11 )
12 );
DNAME EMP_NAME EMP_NUM
------------------------------ -------------------- ----------
ACCOUNTING CLARK 7782
ACCOUNTING KING 7839
ACCOUNTING MILLER 7934
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment