Skip to Main Content
  • Questions

  • Generating JSON data for data in given table dynamically

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Prabhakar.

Asked: May 06, 2020 - 5:46 am UTC

Last updated: May 11, 2020 - 5:14 am UTC

Version: 11G

Viewed 1000+ times

You Asked

My goal is to generate the data in JSON format from one table. The table might have 10 millions records. Another challenge is I am not aware of the number of columns and column names of the table for which I need to generate the JSON.

My DB server is oracle 11G. But the solution need to work on 10G too.

Below is my solution.

1. I generate the JSON string with column name and bind variable by looping through the columns collection of cursor - This is achieved
2. Then loop through the rows and filled the values of bind variables with column data - This is not working for dynamic columns

Below is sample code. I have table named CITY having columns "NAme" and "STATE"

It would be helpful if you guide me on below issue or point to any other solution. Thanks in advance


DECLARE

   -- cursor
  CURSOR c_city IS SELECT  *  FROM archival.cities ;
  
   -- record    
   r_city c_city%ROWTYPE;
   final_text varchar(2000):='';
   final_str varchar(2000):='';
BEGIN
  
  
  final_text:='select ''{name:''||:1||'',state:''||:2||''}'' as  from dual';  --This string i am able to generate it and hence not giving the code for it.
  
  OPEN c_city;

  LOOP
    FETCH  c_city  INTO r_city;
    EXIT WHEN c_city%NOTFOUND;
     execute immediate final_text  into final_str using r_city.name,r_city.state; --Here is the problem. I want r_city.name,r_city.state to be dynamically generated and use it to replace the values. 
        
    DBMS_OUTPUT.PUT_LINE(final_str);

   
  END LOOP;

  CLOSE c_city;
END;



and Chris said...

Pre-12c the easiest way is:

- Install APEX
- Use APEX_JSON

You can pass this a ref cursor and it'll automatically convert the results to JSON:

declare
  cur sys_refcursor;
begin
  
  open cur for
    select employee_id as "employeeId", 
           first_name || ' ' || last_name as "empName"
    from   hr.employees
    where  department_id = 10;

  apex_json.initialize_clob_output;

  apex_json.open_object;
  apex_json.write ( 'employees', cur );
  apex_json.close_object;

  dbms_output.put_line(apex_json.get_clob_output);
  apex_json.free_output;
  
  open cur for
    select department_name "departmentName",
           cursor (
             select employee_id "employeeId" 
             from   hr.employees e
             where  e.department_id = d.department_id
           ) "employees"
    from   hr.departments d
    where  department_id in ( 10, 20 );

  apex_json.initialize_clob_output;

  apex_json.open_object;
  apex_json.write ( 'departments', cur );
  apex_json.close_object;

  dbms_output.put_line(apex_json.get_clob_output);
  apex_json.free_output;
    
end;
/

{
"employees":[
{
"employeeId":200
,"empName":"Jennifer Whalen"
}
]
}

{
"departments":[
  {"departmentName":"Administration","employees":[{"employeeId":200}]},
  {"departmentName":"Marketing","employees":[{"employeeId":201},{"employeeId":202}]}]
}

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Useful tip for json generation

Prabhakar Bhosale, May 09, 2020 - 6:55 am UTC

HI Chris,
Thanks for details and tips for JSON generation. Will definitely try out this one. Thx

Regards
Prabhakar

Steps to install APEX on 12.1

A reader, May 09, 2020 - 5:09 pm UTC

Hi Chris,
Could you please share steps to install APEX on Oracle 12.1 Enterprise edition.
Connor McDonald
May 11, 2020 - 5:14 am UTC

1) download it (just search for application express downloads)
2) conn as sysdba to your pluggable
3) @apexins SYSAUX SYSAUX TEMP /i/

And of course, as always, carefully read the installation docs before starting anything

using SQLCL

Rajeshwaran, Jeyabal, May 12, 2020 - 4:03 am UTC

other option to make use of SQL Developer command line (SQLCL) - and make use of "sqlformat" option like this:

SQL> set sqlformat json

SQL> select * from dept;
{"results":[{"columns":[{"name":"DEPTNO","type":"NUMBER"},{"name":"DNAME","type":"VARCHAR2"},{"name":"LOC","type":"VARCHAR2"}],"items":
[
{"deptno":10,"dname":"ACCOUNTING","loc":"NEW YORK"}
,{"deptno":20,"dname":"RESEARCH","loc":"DALLAS"}
,{"deptno":30,"dname":"SALES","loc":"CHICAGO"}
,{"deptno":40,"dname":"OPERATIONS","loc":"BOSTON"}
]}]}

SQL>  select employee_id as "employeeId",
  2             first_name || ' ' || last_name as "empName"
  3      from   hr.employees
  4      where  department_id = 10;
{"results":[{"columns":[{"name":"employeeId","type":"NUMBER"},{"name":"empName","type":"VARCHAR2"}],"items":
[
{"employeeid":200,"empname":"Jennifer Whalen"}
]}]}

SQL> select department_name "departmentName",
  2             cursor (
  3               select employee_id "employeeId"
  4               from   hr.employees e
  5               where  e.department_id = d.department_id
  6             ) "employees"
  7      from   hr.departments d
  8      where  department_id in ( 10, 20 )
  9  /
{"results":[{"columns":[{"name":"departmentName","type":"VARCHAR2"},{"name":"employees","type":"REFCURSOR"}],"items":
[
{"departmentname":"Administration","employees":[
{"employeeid":200}
]}
,{"departmentname":"Marketing","employees":[
{"employeeid":201}
,{"employeeid":202}
]}
]}]}

SQL>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.