I am on 11g2. Trying to generate JSON strings for various queries. However, I could not generate the targeted string structure, which looks like this:
[{
"key": {
"_id": "keyVal1"
},
"values": {
"col1": "val1",
"col4": "val2"
}
}, {
"key": {
"_id": "keyVal2"
},
"values": {
"col1": "val1",
"col4": "val2"
}
}]
So it is basically, a list of objects (say, outer objects), where each outer object, contains two inner objects inside (key & values). My goal is convert each row of my query into an outer object with objects inside.
I do not want to hardcode the structure, as it will be used with many queries. I am trying to find a flexible way to do it. My best source so far was http://ora-00001.blogspot.my/2010/02/ref-cursor-to-json.html
I thought the last example using cursor expression would help me:
declare
l_json clob;
begin
l_json := json_util_pkg.sql_to_json ('select d.deptno, d.dname,
cursor (select e.*
from emp e
where e.deptno = d.deptno) as the_emps
from dept d');
dbms_output.put_line (substr(l_json, 1, 10000));
end;
But unfortunately, it was not what I am looking for. Using cursor expression generates a list of objects, and inside each object there is another list. I want to generate two objects inside each object.
I tried to play with the query, in futile.
Is there any dynamic way I can generate the structure above?
UPDATE:
Here is example of what I am trying to achieve. Suppose the table is:
createtableemp(
empno number(4,0),
ename varchar2(10),
job varchar2(9),
mgr number(4,0),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2,0)
);
insert into emp
values(
7839, 'KING', 'PRESIDENT', null,
to_date('17-11-1981','dd-mm-yyyy'),
5000, null, 10
);
insert into emp
values(
7698, 'BLAKE', 'MANAGER', 7839,
to_date('1-5-1981','dd-mm-yyyy'),
2850, null, 30
);
insert into emp
values(
7782, 'CLARK', 'MANAGER', 7839,
to_date('9-6-1981','dd-mm-yyyy'),
2450, null, 10
);
insert into emp
values(
7566, 'JONES', 'MANAGER', 7839,
to_date('2-4-1981','dd-mm-yyyy'),
2975, null, 20
);
insert into emp
values(
7788, 'SCOTT', 'ANALYST', 7566,
to_date('13-JUL-87','dd-mm-rr') - 85,
3000, null, 20
);
insert into emp
values(
7902, 'FORD', 'ANALYST', 7566,
to_date('3-12-1981','dd-mm-yyyy'),
3000, null, 20
);
insert into emp
values(
7369, 'SMITH', 'CLERK', 7902,