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>