1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

WITH dep AS (SELECT DEPT.*,
                    JSON_OBJECT('DEP_CODE' VALUE DEPTNO, 'DEP_CNAME' VALUE DNAME) AS json_result
             FROM DEPT),
     em AS (SELECT DEPTNO,
                   JSON_ARRAYAGG(JSON_OBJECT('EMP_CODE' VALUE EMPNO, 'EMP_CNAME' VALUE ENAME)) AS json_result
            FROM EMP
            GROUP BY DEPTNO)
SELECT dep.DNAME,
       JSON_OBJECT('dep_code' VALUE dep.DEPTNO,
                   'dep_cname' VALUE DNAME,
                   'emps' VALUE em.json_result
       ) AS json_result

FROM dep
         LEFT JOIN em ON dep.DEPTNO = em.DEPTNO
;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

DECLARE
    l_clob1    CLOB;
BEGIN
    apex_json.initialize_clob_output;
    apex_json.open_object;
    FOR dep IN (SELECT * FROM DEPT )
        LOOP
            apex_json.write('dep_code', TO_CHAR(dep.DEPTNO), TRUE);
            apex_json.write('dep_cname', TO_CHAR(dep.DNAME), TRUE);
            apex_json.open_array('emps');
            FOR em IN (
                SELECT * FROM EMP WHERE DEPTNO = dep.DEPTNO
                )
                LOOP
                    apex_json.open_object;
                    apex_json.write('EMP_CODE', TO_CHAR(em.EMPNO), TRUE);
                    apex_json.write('EMP_CNAME', em.ENAME, TRUE);
                    apex_json.close_object;
                END LOOP;
            apex_json.close_array;
        END LOOP;
    apex_json.close_object;
    l_clob1 := apex_json.get_clob_output;
    apex_json.free_output;
    dbms_output.put_line('---------------------------');
    dbms_output.put_line(l_clob1);
    dbms_output.put_line('---------------------------');
END;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

{
    "dep_code": 10,
    "dep_cname": "ACCOUNTING",
    "emps": [
        {
            "EMP_CODE": 7839,
            "EMP_CNAME": "KING"
        },
        {
            "EMP_CODE": 7934,
            "EMP_CNAME": "MILLER"
        },
        {
            "EMP_CODE": 7782,
            "EMP_CNAME": "CLARK"
        }
    ]
}

1

{"data":[{"project_id":"AAA-AEP(GW)","amount":4832.7901},{"project_id":"BBBB-AEP(PQ)","amount":1823.2099}]}

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT jt.amount
FROM REAGENT_INCOME_PLAN t,
     JSON_TABLE(t.MAX_PROJECT_AMOUNT, '$.data[*]'
                COLUMNS (
                    project_id VARCHAR2(50) PATH '$.project_id',
                    amount NUMBER PATH '$.amount'
                    )
     ) jt
WHERE id = :P184_INCOME_PLAN_ID
  AND jt.project_id = 'AAA-AEP(GW)';