Formas de leer un JSON en Oracle – APEX_JSON

Formas de leer un JSON en Oracle

• Usando JSON_TABLE

• Usando 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

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.

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;

A modo de práctica varemos como imprimir estas claves del JSON.

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;

Ahora empezaremos a obtener los valores de cada nodo.

Primero los del objeto superior, como todos los elementos son objetos es más sencillo.

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;

Para el array de datos necesitaremos hacer un loop.

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.

Referencias:

Mas información de este package lo podemos encontrar en:

• Oracle Doc Ref

• Oracle Base

• Stack overflow – Acceso a datos específicos

• Otros ejemplos