JSON and Oracle Database part 2| كل ما نحتاجه من تماربن
drop table emp_json;
create table emp_json
( seq number primary key,
empdata clob,
constraint emp_check_json check (empdata is json)
);
insert into emp_json values (1,'test data');
insert into emp_json values
(1,'{ "employees":
[
{
"name": "ali ahmed",
"position": "Software Engineer",
"salary": 75000
},
{
"name": "Jane Smith",
"position": "Product Manager",
"salary": 90000
},
{
"name": "salim naser",
"position": "GM Manager",
"salary": 20000
},
{
"name": "khaled alkhudari",
"position": "programmer",
"salary": 20000
}
]
}');
select * from emp_json
-- we need to pick the first employee details
select seq,JSON_VALUE(e.empdata,'$.employees[0].name') as name,
JSON_VALUE(e.empdata,'$.employees[0].position') as position,
JSON_VALUE(e.empdata,'$.employees[0].salary') as salary
from emp_json e
select seq, JSON_VALUE(e.empdata,'$.employees[1].name' ) as name,
JSON_VALUE(e.empdata,'$.employees[1].position') as position,
JSON_VALUE(e.empdata,'$.employees[1].salary') as salary
from emp_json e
--we need to pick the json for first employee
select JSON_query(e.empdata,'$.employees[0]')
from emp_json e
--we need to pick all data as relational database
select d.seq, j.name, j.position, j.salary
from emp_json d,
json_table(d.empdata,'$'
columns (
nested employees[*]
columns(name, position, salary)
)
) j
select d.seq, j.name, j.position, j.salary
from emp_json d,
json_table(d.empdata,
'$' columns (
nested employees[0,2]
columns(name, position, salary)
)
) j
select d.seq, j.name, j.position, j.salary
from emp_json d,
json_table(d.empdata,
'$' columns (
nested employees[3]
columns(name, position, salary)
)
) j
select e.employee_id, e.first_name, e.last_name
from employees e
where department_id=30
select json_object
(
'employees' value json_arrayagg
(
json_object ('employee_id' value employee_id,
'first_name' value first_name,
'last_name' value last_name
)
)
)
from employees
where department_id=30