Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to create JSON list with nested objects from SQL

Hawk333Aug 19 2016 — edited Aug 23 2016

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,

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 20 2016
Added on Aug 19 2016
10 comments
1,087 views