# Formas de leer un JSON en Oracle - APEX_JSON # Formas de leer un JSON en Oracle - APEX_JSON ## Formas de leer un JSON en Oracle * [Usando JSON_TABLE][blog_json_table] * [Usando APEX_JSON][blog_apex_json] Para leer un formato tipo JSON dentro de ORACLE existen diferentes formas una de ellas es usando APEX_JSON. También podemos usar JSON_TABLE como vemos en este otro blog. En el siguiente blog daré algunas formas de leer los diferentes atributos usando PL-SQL. Tomaremos de ejemplo el siguiente JSON. En el script completo dejare toda la demo incluida la creación de la tabla para la prueba. [Script Ejemplo 1][script_1] [script_1]: https://drive.google.com/file/d/1I78vyN6E7-UJkPS0a6yFZyA-f5_9qlUF/view?usp=sharing [Script Ejemplo 2][script_2] [script_2]: https://drive.google.com/file/d/1I859Xnb8VQ62UasmmM6w9qLTPdi5N_5F/view?usp=share_link JSON: ``` json { "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" ] } ] } ``` Empezaremos con determinar cuantas claves(Keys) tenemos en cada objeto. ```sql declare l_json demo_json.json_column%type; l_cantidad number; json_content apex_json.t_values; l_members apex_t_varchar2; l_sub_array apex_t_varchar2; begin -- retornamos el JSON de ejemplo dentro de l_json select json_column into l_json from demo_json; -- paseamos el json con apex_json.parse apex_json.parse(json_content, l_json); -- cantidad de claves del objeto principal l_cantidad := apex_json.get_count(p_path => '.' ,p_values => json_content); dbms_output.put_line ('Cantidad de claves : '); -- 4 atributos dbms_output.put_line ('.. en el objeto principal ' || l_cantidad ); -- 4 atributos dbms_output.put_line ('.. dentro del objeto "attributes" ' || apex_json.get_count(p_path=> 'attributes' , p_values => json_content ) ); dbms_output.put_line ('.. dentro del objeto "attributes.Address" ' || apex_json.get_count(p_path=> 'attributes.Address' , p_values => json_content ) ); l_members := apex_json.get_members(p_path=> 'members[1]' , p_values => json_content ); dbms_output.put_line ('.. dentro del sub array members[1] ' || l_members.count ); -- Note que aquí es muy diferente para sacar la cantidad de valores dentro del array power l_sub_array := apex_json.get_t_varchar2 (p_path=> 'members[3].powers' , p_values => json_content ); dbms_output.put_line ('.. dentro del sub array "members[1].powers" ' || l_sub_array.count ); -- igual diferente para obtener la cantidad de los objetos dentro del array members l_cantidad := apex_json.get_count (p_path=> 'members' , p_values => json_content ); dbms_output.put_line ('.. dentro del sub array "members" ' || l_cantidad ); end; ``` ![run_code_1] A modo de práctica varemos como imprimir estas claves del JSON. ```sql declare l_json demo_json.json_column%type; l_cantidad number; json_content apex_json.t_values; l_members apex_t_varchar2; l_sub_array apex_t_varchar2; begin -- retornamos el JSON de ejemplo dentro de l_json select json_column into l_json from demo_json; -- paseamos el json con apex_json.parse apex_json.parse(json_content, l_json); -- cantidad de claves del objeto principal l_cantidad := apex_json.get_count(p_path => '.' ,p_values => json_content); dbms_output.put_line ('Cantidad de claves : '); -- 4 atributos dbms_output.put_line ('-------------------------------------------' ); dbms_output.put_line ('.. en el objeto principal ' || l_cantidad ); -- 4 atributos l_members := apex_json.get_members(p_path=> '.' , p_values => json_content ); for i in 1 .. l_cantidad loop dbms_output.put_line ('l_members: ' || l_members(i) ); end loop; dbms_output.put_line ('-------------------------------------------' ); dbms_output.put_line ('.. dentro del objeto "attributes" ' || apex_json.get_count(p_path=> 'attributes' , p_values => json_content ) ); l_members := apex_json.get_members(p_path=> 'attributes' , p_values => json_content ); for i in 1 .. l_members.count loop dbms_output.put_line ('attributes: ' || l_members(i) ); end loop; dbms_output.put_line ('-------------------------------------------' ); dbms_output.put_line ('.. dentro del objeto "attributes.Address" ' || apex_json.get_count(p_path=> 'attributes.Address' , p_values => json_content ) ); l_members := apex_json.get_members(p_path=> 'attributes.Address' , p_values => json_content ); for i in 1 .. l_members.count loop dbms_output.put_line ('attributes.Address: ' || l_members(i) ); end loop; dbms_output.put_line ('-------------------------------------------' ); l_members := apex_json.get_members(p_path=> 'members[1]' , p_values => json_content ); dbms_output.put_line ('.. dentro del sub array members[1] ' || l_members.count ); for i in 1 .. l_members.count loop dbms_output.put_line ('members[1]: ' || l_members(i) ); end loop; dbms_output.put_line ('-------------------------------------------' ); -- Note que aquí es muy diferente para sacar la cantidad de valores dentro del array power l_sub_array := apex_json.get_t_varchar2 (p_path=> 'members[3].powers' , p_values => json_content ); dbms_output.put_line ('.. dentro del sub array "members[1].powers" ' || l_sub_array.count ); for i in 1 .. l_sub_array.count loop dbms_output.put_line ('members[3].powers: ' || l_sub_array(i) ); end loop; dbms_output.put_line ('-------------------------------------------' ); -- Obtener la cantidad de los objetos dentro del array members l_cantidad := apex_json.get_count (p_path=> 'members' , p_values => json_content ); dbms_output.put_line ('.. dentro del sub array "members" ' || l_cantidad ); end; ``` ![run_code_2] Ahora empezaremos a obtener los valores de cada nodo. Primero los del objeto superior, como todos los elementos son objetos es más sencillo. ```sql declare l_json demo_json.json_column%type; l_boolean boolean; json_content apex_json.t_values; begin -- retornamos el JSON de ejemplo dentro de l_json select json_column into l_json from demo_json; -- paseamos el json con apex_json.parse apex_json.parse(json_content, l_json); dbms_output.put_line ('---------------- Nodo Principal ---------------------------' ); dbms_output.put_line ('squadName : ' || apex_json.get_varchar2(p_path => 'squadName' ,p_values => json_content) ); dbms_output.put_line ('active : ' || apex_json.get_varchar2 (p_path => 'active' ,p_values => json_content)); l_boolean := apex_json.get_boolean (p_path => 'active' ,p_values => json_content); dbms_output.put_line ('active : ' || case when l_boolean = true then 'value true' else 'value false' end ); dbms_output.put_line ('---------------- Sub Nodo attributes ---------------------------' ); dbms_output.put_line ('formed : ' || apex_json.get_varchar2(p_path => 'attributes.formed' ,p_values => json_content) ); dbms_output.put_line ('secretBase : ' || apex_json.get_varchar2(p_path => 'attributes.secretBase' ,p_values => json_content) ); dbms_output.put_line ('----------------- Sub Nodo Address --------------------------' ); dbms_output.put_line ('city : ' || apex_json.get_varchar2(p_path => 'attributes.Address.city' ,p_values => json_content) ); dbms_output.put_line ('zipCode : ' || apex_json.get_number(p_path => 'attributes.Address.zipCode' ,p_values => json_content) ); dbms_output.put_line ('country : ' || apex_json.get_varchar2(p_path => 'attributes.Address.country' ,p_values => json_content) ); end; ``` ![run_code_3] Para el array de datos necesitaremos hacer un loop. ```sql declare l_json demo_json.json_column%type; l_boolean boolean; json_content apex_json.t_values; l_cantidad number; l_members apex_t_varchar2; begin -- retornamos el JSON de ejemplo dentro de l_json select json_column into l_json from demo_json; -- paseamos el json con apex_json.parse apex_json.parse(json_content, l_json); dbms_output.put_line ('---------------- Array Members ---------------------------' ); l_cantidad := apex_json.get_count (p_path=> 'members' , p_values => json_content ); for i in 1 .. l_cantidad loop dbms_output.put_line ('---------------- Objeto: ' ||i|| '---------------------------' ); dbms_output.put_line ('name: ' || apex_json.get_varchar2 (p_path => 'members[%d].name', p0 => i, p_values => json_content) ); dbms_output.put_line ('age: ' || apex_json.get_varchar2 (p_path => 'members[%d].age', p0 => i, p_values => json_content) ); dbms_output.put_line ('secretIdentity: ' || apex_json.get_varchar2 (p_path => 'members[%d].secretIdentity', p0 => i, p_values => json_content) ); dbms_output.put_line ('................ Poderes ............' ); -- note que aquí usaremos get_t_varchar2 un array de string para almacenar los poderes l_members := apex_json.get_t_varchar2 (p_path => 'members[%d].powers', p0 => i, p_values => json_content ); for i in 1 .. l_members.count loop dbms_output.put_line ('powers: ' || l_members(i) ); end loop; end loop; end; ``` Y de esta habremos podido acceder a todos los niveles y datos de nuestro objeto JSON. ![run_code_4] #### Referencias: Mas información de este package lo podemos encontrar en: * [Oracle Doc Ref][referencia_1] * [Oracle Base][oracle_base] * [Stack overflow - Acceso a datos específicos][Stackoverflow] * [Otros ejemplos][Otros ejemplos] [referencia_1]: https://docs.oracle.com/en/database/oracle/apex/22.1/aeapi/APEX_JSON.html#GUID-11919ED6-CE3D-4497-8733-F56CD27B6BFF [oracle_base]: https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in-oracle [Stackoverflow]: https://stackoverflow.com/questions/39839701/apex-json-get-varchar2-in-pl-sql [Otros ejemplos]: https://www.rittmanmead.com/blog/2018/08/parsing-badly-formatted-json-in-oracle-db-with-apex_json/ [blog_json_table]: https://aflorestorres.com/2023/03/12/formas-de-leer-un-json-en-oracle-json_table/ [blog_apex_json]: https://www.rittmanmead.com/blog/2018/08/parsing-badly-formatted-json-in-oracle-db-with-apex_json/ [run_code_1]: https://drive.google.com/uc?id=1Joh-FIooz0LbQYq2xWw7j0jb5DJyMrWv [run_code_2]: https://drive.google.com/uc?id=1Jq5RTTee_Ks93IHxwuQTFkoIZX7zx8Wz [run_code_3]: https://drive.google.com/uc?id=1Jqa7QVdLRNUB8A7BRPy0VJS-hpJZbtAc [run_code_4]: https://drive.google.com/uc?id=1JrrpoC2dpj6Y-INBjJNXB4-ZkTediXun