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:
create
table
emp(
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,