Apex · Oracle · REST

Generar un JSON en Oracle APEX

Para generar un JSON veremos dos formas, la primera usando APEX_JSON y la otra usando JSON_TABLE.

Mencionar que para usar APEX_JSON tambien lo podremos hacer usando cursores (seletcs directo a una tabla).

Recrearemos el JSON que usamos en previos post:

{
  "squadName": "Super hero squad",
  "active"   : true,
  "attributes" : {
                  "formed"    : 2016,
                  "secretBase": "Super tower",
                  "Address"   : {
                                  "city"    : "South San Francisco",
                                  "zipCode" : 99236,
                                  "country" : "United States of America"
                                }
  },
  "members": [
    {
      "name"          : "Molecule Man",
      "age"           : 29,
      "secretIdentity": "Dan Jukes",
      "powers"        : ["Radiation resistance",
                         "Turning tiny"
                        ]
    },
    {
      "name"          : "Madame Uppercut",
      "age"           : 39,
      "secretIdentity": "Jane Wilson",
      "powers"        : ["Million tonne punch"
                        ]
    },
    {
      "name"          : "Eternal Flame",
      "age"           : 1000000,
      "secretIdentity": null,
      "powers"        : ["Immortality",
                          "Heat Immunity",
                          "Inferno",
                          "Teleportation",
                          "Interdimensional travel"
                        ]
    }
  ]
}

Usando APEX_JSON, el código se hace extenso por que estamos construyendo linea por linea, esto seria mas sencillo si usamos cursores.


declare
  l_json_clob  CLOB;
begin
  -- Initialize JSON
  apex_json.initialize_clob_output (p_indent => 0);
  apex_json.open_object; -- {
  apex_json.write('squadName', 'Super hero squad');
  apex_json.write('active', true);

  apex_json.open_object('attributes');
    apex_json.write('formed', 2016);
    apex_json.write('secretBase', 'Super tower');

    apex_json.open_object('Address');
      apex_json.write('city', 'South San Francisco');
      apex_json.write('zipCode', 99236);
      apex_json.write('country', 'United States of America');
    apex_json.close_object; -- }

  apex_json.close_object; -- }


  apex_json.open_array('members');

    apex_json.open_object; -- {
    apex_json.write('name', 'Molecule Man');
    apex_json.write('age', 29);
    apex_json.write('secretIdentity', 'Dan Jukes');

      apex_json.open_array('powers');
        apex_json.write('Radiation resistance');
        apex_json.write('Turning tiny');
      apex_json.close_array; -- ]

    apex_json.close_object; -- }

    apex_json.open_object; -- {
    apex_json.write('name', 'Madame Uppercut');
    apex_json.write('age',  39);
    apex_json.write('secretIdentity', 'Jane Wilson');

      apex_json.open_array('powers');
        apex_json.write('Million tonne punch');
      apex_json.close_array; -- ]
    apex_json.close_object; -- }

    apex_json.open_object; -- {
    apex_json.write('name', 'Eternal Flame');
    apex_json.write('age',  1000000);
   apex_json.write('secretIdentity', '',true);

      apex_json.open_array('powers');
        apex_json.write('Immortality');
        apex_json.write('Heat Immunity');
        apex_json.write('Inferno');
        apex_json.write('Teleportation');
        apex_json.write('Interdimensional travel');
      apex_json.close_array; -- ]
    apex_json.close_object; -- }

  apex_json.close_array; -- ] items
  -- close out json and return clob.
  apex_json.close_object; -- }
  l_json_clob := apex_json.get_clob_output;
  dbms_output.put_line(APEX_JSON.get_clob_output);
  apex_json.free_output;
end;

Esta linea dbms_output.put_line(APEX_JSON.get_clob_output); no es necesaria solo para imprimir los valores en pantalla.

Usando la tabla emp y dept para construir un JSON con cursores.

declare
  l_cursor sys_refcursor;
begin
  open l_cursor for
    select d.deptno as "department_number"
         , d.dname  as "department"
         , d.loc    as "location"
         , cursor(select e.empno as "employee_number"
                       , e.ename as "employee"
                    from emp e
                   where e.deptno = d.deptno
                   order by e.empno) as "employees"
      from dept d;

  apex_json.initialize_clob_output;

  apex_json.open_object;
  apex_json.write('departments', l_cursor);
  apex_json.close_object;

  dbms_output.put_line(apex_json.get_clob_output);
  apex_json.free_output;
end;

Resultado:

{
  "departments": [
    {
      "department_number": 10,
      "department": "ACCOUNTING",
      "location": "NEW YORK",
      "employees": [
        {
          "employee_number": 7782,
          "employee": "CLARK"
        },
        {
          "employee_number": 7839,
          "employee": "KING"
        }
      ]
    },
    {
      "department_number": 20,
      "department": "RESEARCH",
      "location": "DALLAS",
      "employees": [
        {
          "employee_number": 7369,
          "employee": "SMITH"
        },
        {
          "employee_number": 7566,
          "employee": "JONES"
        }
      ]
    },
    {
      "department_number": 30,
      "department": "SALES",
      "location": "CHICAGO",
      "employees": [
        {
          "employee_number": 7499,
          "employee": "ALLEN"
        },
        {
          "employee_number": 7521,
          "employee": "WARD"
        }
      ]
    },
    {
      "department_number": 40,
      "department": "OPERATIONS",
      "location": "BOSTON",
      "employees": null
    }
  ]
}

Ahora crearemos el mismo JSON usando JSON_OBJECT_T Y JSON_ARRAY_T

declare
  l_main_obj        json_object_t := json_object_t();
  l_adress_obj      json_object_t := json_object_t();
  l_attrib_obj      json_object_t := json_object_t();
  l_member_obj      json_object_t := json_object_t();
  l_members_array   json_array_t  := json_array_t ();
  l_powers_array    json_array_t  := json_array_t ();

  l_json_clob       clob;
begin
    l_main_obj.put('squadName', 'Super hero squad');
    l_main_obj.put('active', true);

      l_adress_obj.put('city','South San Francisco');
      l_adress_obj.put('zipCode', 99236);
      l_adress_obj.put('country','United States of America');

        l_attrib_obj.put('formed', 2016);
        l_attrib_obj.put('secretBase', 'Super tower');
        l_attrib_obj.put('Address', l_adress_obj);

    l_main_obj.put('attributes', l_attrib_obj);

    -- creando los objetos del array
    -- objeto 1 de array
    l_member_obj.put('name', 'Molecule Man');
    l_member_obj.put('age', 29);
    l_member_obj.put('secretIdentity', 'Dan Jukes');
    l_powers_array.append('Radiation resistance');
    l_powers_array.append('Turning tiny');
    l_member_obj.put('powers', l_powers_array );
    l_members_array.append(l_member_obj);

    -- objeto 2 de array
    l_member_obj.put('name', 'Madame Uppercut');
    l_member_obj.put('age', 39);
    l_member_obj.put('secretIdentity', 'Jane Wilson');
    l_powers_array.append('Million tonne punch');
    l_member_obj.put('powers', l_powers_array );
    l_members_array.append(l_member_obj);

    -- objeto 3 de array
    l_member_obj.put('name', 'Eternal Flame');
    l_member_obj.put('age', 1000000);
    l_member_obj.put('secretIdentity', '');
    l_powers_array.append('Immortality');
    l_powers_array.append('Heat Immunity');
    l_powers_array.append('Inferno');
    l_powers_array.append('Teleportation');
    l_powers_array.append('Interdimensional travel');
    l_member_obj.put('powers', l_powers_array );
    l_members_array.append(l_member_obj);

    --- agregando el array al objecto principal
    l_main_obj.put('members', l_members_array);

  l_json_clob := l_main_obj.to_clob;
  dbms_output.put_line(l_json_clob);
end;

Tambien se puede crear a partir de un select.

  select json_object (
          key 'empno' value (
          select json_arrayagg(json_object (*) returning clob)
            from emp
          ) returning clob
         )
    from dual;

Resultado:

{
  "EMPNO": [
    {
      "EMPNO": 7839,
      "ENAME": "KING",
      "JOB": "PRESIDENT",
      "MGR": null,
      "HIREDATE": "1981-11-17T00:00:00",
      "SAL": 5000,
      "COMM": null,
      "DEPTNO": 10
    },
    {
      "EMPNO": 7698,
      "ENAME": "BLAKE",
      "JOB": "MANAGER",
      "MGR": 7839,
      "HIREDATE": "1981-05-01T00:00:00",
      "SAL": 2850,
      "COMM": null,
      "DEPTNO": 30
    }, ....
  ]
}

Referencias

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s