return JSON_OBJECT_T response instead of CLOB in PLSQL

Question

I want to return JSON OBJECT but I have a code which returns CLOB, how can I modify below such that I return the JSON_OBJECT_T response instead of clob . Please help

DECLARE
  p_json CLOB;
BEGIN
  SELECT JSON_SERIALIZE(
           JSON_OBJECT(
             KEY 'employees' VALUE JSON_ARRAYAGG(
               JSON_OBJECT(
                 KEY 'empno' VALUE id,
                 KEY 'ename' VALUE name,
                 KEY 'job'   VALUE email_id
               )
               ORDER BY id
             )
           )
           RETURNING CLOB
         ) AS json
  INTO   p_json
  FROM   employee_tbl;
  DBMS_OUTPUT.PUT_LINE(p_json);
END;
/

Output expected:

{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}

Sample data:

CREATE TABLE employee_tbl (id, name, email_id) AS
  SELECT 1065, 'Abu',  'email1@example.com' FROM DUAL UNION ALL
  SELECT 1066, 'Umar', 'email2@example.com' FROM DUAL

Answer 1

the SQL Function JSON_OBJECT returns VARCHAR2, CLOB or BLOB (see docs)- (JSON can only be returned as from version 21). Convert the result to a json object using JSON_OBJECT_T.PARSE.

DECLARE
  l_json_object JSON_OBJECT_T;
  l_json CLOB;
BEGIN
   SELECT
      JSON_OBJECT(
         KEY 'employees' VALUE 
            JSON_ARRAYAGG(
               JSON_OBJECT(KEY 'empno' VALUE id
                           ,KEY 'ename' VALUE name
                           ,KEY 'job' VALUE email_id)
               ORDER BY id) 
            RETURNING CLOB)
     INTO l_json
     FROM employee_tbl;
   -- now convert clob to JSON_OBJECT_T
   l_json_object := JSON_OBJECT_T.PARSE(l_json);
   -- or...this works too
   -- l_json_object := JSON_OBJECT_T(l_json);

   -- check if value is correct
   dbms_output.put_line(l_json_object.to_clob);
END;
/


{"employees":[{"empno":1065,"ename":"Abu","job":"email1@example.com"},{"empno":1066,"ename":"Umar","job":"email2@example.com"}]}


PL/SQL procedure successfully completed.