Generating JSON with multiple functions using Oracle APEX_JSON

Question

Currently I am generating some jsons with data with oracle for backend purposes and I'm struggling with complex and repetetive structions that I have to process manually.

For example I have this array of objects:

{
  "infoColumnsWidgets": [
    {
      "widgetNamespace": "mot",
      "widgetName": "info_column",
      "orderNumber": 1,
      "navigateToPage": null,
      "widgetData": {
        "title": "Fact",
        "textPattern": "$v0",
        "values": [
          {
            "id": "v0",
            "type": "int",
            "value": "200000"
          }
        ]
      }
    },
    {
      "widgetNamespace": "mot",
      "widgetName": "info_column",
      "orderNumber": 2,
      "navigateToPage": null,
      "widgetData": {
        "title": "Plan",
        "textPattern": "$v0",
        "values": [
          {
            "id": "v0",
            "type": "int",
            "value": "200000"
          }
        ]
      }
    },
    {
      "widgetNamespace": "mot",
      "widgetName": "info_column",
      "orderNumber": 3,
      "navigateToPage": null,
      "widgetData": {
        "title": "Prognosis",
        "textPattern": "$v0",
        "values": [
          {
            "id": "v0",
            "type": "int",
            "value": "100"
          }
        ]
      }
    }
  ]
}

Certainly I generate it in a loop but this structure occurs often and I'd prefer to put it into some function to do the following:

function f_getTest return clob as
  v_res clob;
begin
  apex_json.initialize_clob_output;
  apex_json.open_object;
    apex_json.open_object('infoColumnsWidgets');
    for rec in (select * from some_table_data)
    loop
      apex_json.write_raw(f_getWidgetJson(rec.param));
    end loop;
    apex_json.close_object;
  apex_json.close_all;
  v_res := apex_json.get_clob_output;
  apex_json.free_output;
  return v_res;
end;

But as far as I know there is no option to put one json into another using apex_json. I can try with some weird workarounds with putting some placeholders and replacing them in final clob but no, I don't want, please, don't make me do that.

Any ideas are super welcome

Answer 1

Does this help ? I took the example from oracle-base and moved the body code into a separate procedure. In the example below it is an inline procedure but nothing stops you from putting into a standalone procedure or a package.

DECLARE
  PROCEDURE dept_object 
  IS
    l_cursor SYS_REFCURSOR;
  BEGIN
    OPEN l_cursor FOR
      SELECT d.dname AS "department_name",
             d.deptno AS "department_number",
             CURSOR(SELECT e.empno AS "employee_number",
                           e.ename AS "employee_name"
                    FROM   emp e
                    WHERE  e.deptno = d.deptno
                    ORDER BY e.empno) AS "employees"
      FROM   dept d
      ORDER BY d.dname;
    APEX_JSON.open_object;
    APEX_JSON.write('departments', l_cursor);   
    APEX_JSON.close_object;         
  END;
BEGIN
  APEX_JSON.initialize_clob_output;  
  dept_object;
  DBMS_OUTPUT.put_line(APEX_JSON.get_clob_output);
  APEX_JSON.free_output;
END;
/