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
A little knowledge to share (Oracle APEX) 