Skip to Main Content
  • Questions
  • Generating JSON data for data in given table dynamically

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Prabhakar.

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

Answered by: Chris Saxon - Last updated: May 11, 2020 - 5:14 am UTC

Category: PL/SQL - Version: 11G

Viewed 100+ 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 we 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}]}]
}

and you rated our response

  (3 ratings)

Reviews

Useful tip for json generation

May 09, 2020 - 6:55 am UTC

Reviewer: Prabhakar Bhosale

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

May 09, 2020 - 5:09 pm UTC

Reviewer: A reader

Hi Chris,
Could you please share steps to install APEX on Oracle 12.1 Enterprise edition.
Connor McDonald

Followup  

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

May 12, 2020 - 4:03 am UTC

Reviewer: Rajeshwaran, Jeyabal

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 to Explore

SQL

The Oracle documentation contains a complete SQL reference.