To query all EMPLOYEES below the main Departments , and get the result in JSON format .
Connect using SCOTT user
Following are steps:
#1 Create GET method
Type : QUERY
Pagination Size: 0
Connect using SCOTT user
Following are steps:
#1 Create GET method
Type : QUERY
Pagination Size: 0
#2 Add the following code :
SELECT G.Deptno,
G.DName,
CURSOR ( SELECT V.Empno,
V.Ename
FROM EMP V
WHERE V.Deptno = G.Deptno ) EMPLOYEES
FROM DEPT G
Here's the output in PLSQL/Develoepr
check this link : http://nimishgarg.blogspot.com/2020/08/generate-nested-json-from-sql-in-oracle.html
ReplyDeleteSELECT D.DEPARTMENT_ID ID,
JSON_OBJECT (
'DEPTNO' VALUE D.DEPARTMENT_ID,
'DNAME' VALUE D.DEPARTMENT_NAME,
'EMPLOYEES' VALUE
JSON_ARRAYAGG (JSON_OBJECT ('EMPNO' VALUE EMPLOYEE_ID,
'ENAME' VALUE FIRST_NAME,
'JOB' VALUE FIRST_NAME)) FORMAT JSON) JSON_VALUE
FROM hr.departments D, hr.employees E
WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_ID, D.DEPARTMENT_NAME;