json_object - trabajar con json en oracle
leer json en oracle (5)
Cabe señalar que a partir de Oracle 12c hay algún soporte nativo de JSON. Sin embargo, no creo que en la forma actual sea tan útil como PLJSON incluido en otra respuesta.
Para usar la función, cree una tabla con un campo BLOB, CLOB o Varchar2 y agregue una restricción en su contra "columna IS JSON". Esto impone la comprobación de sintaxis JSON en esa columna.
Siempre que la restricción "IS JSON" esté en su lugar, puede acceder a los valores JSON utilizando la notación de puntos desde SQL. Para mí, no parece proporcionar una manipulación tan poderosa como PLJSON. También puede crear un XMLType y luego convertirlo a JSON.
Enlaces útiles:
Documentos de Oracle
Buen tutorial y ejemplos.
Tutorial incluyendo XML a JSON
¿Hay una manera fácil de trabajar con JSON dentro de Oracle? Tengo un procedimiento estándar que utilizo para llamar servicios web con bastante frecuencia. JSON es un formato que conozco en el contexto de desarrollo web, pero ¿cuál es la mejor manera de trabajar con json dentro de un procedimiento almacenado? Por ejemplo, tome la respuesta CLOB del URI, conviértala en un objeto JSON y obtenga un valor de eso.
Para referencia, aquí está el procedimiento que utilicé para obtener las URL.
create or replace procedure macp_URL_GET(url_resp in out clob, v_url in varchar2) is
req Utl_Http.req;
resp Utl_Http.resp;
NAME VARCHAR2 (255);
VALUE VARCHAR2 (1023);
v_msg VARCHAR2 (80);
v_ans clob;
-- v_url VARCHAR2 (32767) := ''http://www.macalester.edu/'';
BEGIN
/* request that exceptions are raised for error Status Codes */
Utl_Http.set_response_error_check (ENABLE => TRUE );
/* allow testing for exceptions like Utl_Http.Http_Server_Error */
Utl_Http.set_detailed_excp_support (ENABLE => TRUE );
/*
Utl_Http.set_proxy (
proxy => ''www-proxy.us.oracle.com'',
no_proxy_domains => ''us.oracle.com''
);
*/
req := Utl_Http.begin_request (url => v_url, method => ''GET'');
/*
Alternatively use method => ''POST'' and Utl_Http.Write_Text to
build an arbitrarily long message
*/
/*
Utl_Http.set_authentication (
r => req,
username => ''SomeUser'',
PASSWORD => ''SomePassword'',
scheme => ''Basic'',
for_proxy => FALSE --this info is for the target Web server
);
*/
Utl_Http.set_header (r => req, NAME => ''User-Agent'', VALUE => ''Mozilla/4.0'');
resp := Utl_Http.get_response (r => req);
/*
DBMS_OUTPUT.put_line (''Status code: '' || resp.status_code);
DBMS_OUTPUT.put_line (''Reason phrase: '' || resp.reason_phrase);
FOR i IN 1 .. Utl_Http.get_header_count (r => resp)
LOOP
Utl_Http.get_header (r => resp, n => i, NAME => NAME, VALUE => VALUE);
DBMS_OUTPUT.put_line (NAME || '': '' || VALUE);
END LOOP;
*/
--test
BEGIN
LOOP
Utl_Http.read_text (r => resp, DATA => v_msg);
--DBMS_OUTPUT.put_line (v_msg);
v_ans := v_ans || v_msg;
url_resp := url_resp || v_msg;
END LOOP;
EXCEPTION
WHEN Utl_Http.end_of_body
THEN
NULL;
END;
--test
Utl_Http.end_response (r => resp);
--url_resp := v_ans;
EXCEPTION
/*
The exception handling illustrates the use of "pragma-ed" exceptions
like Utl_Http.Http_Client_Error. In a realistic example, the program
would use these when it coded explicit recovery actions.
Request_Failed is raised for all exceptions after calling
Utl_Http.Set_Detailed_Excp_Support ( ENABLE=>FALSE )
And it is NEVER raised after calling with ENABLE=>TRUE
*/
WHEN Utl_Http.request_failed
THEN
DBMS_OUTPUT.put_line (
''Request_Failed: '' || Utl_Http.get_detailed_sqlerrm
);
url_resp :=''Request_Failed: '' || Utl_Http.get_detailed_sqlerrm;
/* raised by URL http://xxx.oracle.com/ */
WHEN Utl_Http.http_server_error
THEN
DBMS_OUTPUT.put_line (
''Http_Server_Error: '' || Utl_Http.get_detailed_sqlerrm
);
url_resp := ''Http_Server_Error: '' || Utl_Http.get_detailed_sqlerrm;
/* raised by URL http://otn.oracle.com/xxx */
WHEN Utl_Http.http_client_error
THEN
DBMS_OUTPUT.put_line (
''Http_Client_Error: '' || Utl_Http.get_detailed_sqlerrm
);
url_resp := ''Http_Client_Error: '' || Utl_Http.get_detailed_sqlerrm;
/* code for all the other defined exceptions you can recover from */
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
url_resp := SQLERRM;
END;
Entonces para probarlo
begin
macp_url_get(url_resp => :url_resp,
''http://maps.googleapis.com/maps/api/geocode/json?address=55105&sensor=false'');
end;
(Sé que el googleapi permitirá la respuesta xml, pero hay otras API web que utilizo con regularidad de manera predeterminada para JSON)
Escribí esta biblioteca: http://reseau.erasme.org/pl-sql-library-for-JSON?lang=en , y esto funciona muy bien para obtener alguna respuesta json en una tabla plsql.
Si solo desea extraer datos de Oracle y transformarlos en Json, esta biblioteca es un poco "pesada de usar" ... Puedo proponerle otro código para hacerlo mejor y más rápido:
create or replace package jsonfly as
procedure open_object(k varchar2 default null);
procedure close_object;
procedure open_array (k varchar2 default null);
procedure close_array;
procedure separation;
procedure member(k varchar2, v varchar2);
procedure member(k varchar2, n number);
procedure send;
end;
/
create or replace package body jsonfly as
--------------------------------------------------------------------------------
-- package pour générer du JSON, envoyé à la volé
--------------------------------------------------------------------------------
type tCache is table of varchar2(2000) index by binary_integer;
g_openBrace constant varchar2(2) := ''{ '';
g_closeBrace constant varchar2(2) := '' }'';
g_openBracket constant varchar2(2) := ''[ '';
g_closeBracket constant varchar2(2) := '' ]'';
g_stringDelimiter constant varchar2(1) := ''"'';
g_Affectation constant varchar2(3) := '' : '';
g_separation constant varchar2(3) := '', '';
g_CR constant varchar2(1) := Chr(10); -- used to indent the JSON object correctly
g_spc constant varchar2(2) := '' ''; -- used to indent the JSON object correctly
g_js_comment_open constant varchar2(20) := ''/*-secure-/n''; -- used to prevent from javascript hijacking
g_js_comment_close constant varchar2(20) := ''/n*/''; -- used to prevent from javascript hijacking
--isObjectOpened boolean := false;
--isArrayOpened boolean := false;
t tCache;
i number := 1;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure p(s varchar2) is
begin
t(i) := s;
i := i + 1;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
function encap (s varchar2) return varchar2 is
begin
return g_stringdelimiter || s || g_stringdelimiter;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
function encode_string(p_string varchar2) return varchar2 is
begin
return replace(replace(replace(replace(replace(replace(replace(replace(p_string,
''/', ''//'),
''"'', ''/"''),
''/'', ''//''),
chr(8), ''/b''),
chr(9), ''/t''),
chr(10), ''/n''),
chr(12), ''/f''),
chr(13), ''/r'');
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure open_object(k varchar2 default null) is
begin
if ( k is null ) then
p(g_openbrace);
else
p( encap(k) || g_affectation || g_openbrace);
end if;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure close_object is
begin
if (t(i-1) = g_separation) then
i := i - 1;
end if;
p(g_closebrace);
separation();
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure open_array (k varchar2 default null) is
begin
if ( k is null ) then
p(g_openbracket);
else
p( encap(k) || g_affectation || g_openbracket);
end if;
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure close_array is
begin
if (t(i-1) = g_separation) then
i := i - 1;
end if;
p(g_closebracket);
separation();
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure separation is
begin
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure key(k varchar2) is
begin
p( encap(k) || g_affectation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure value(v varchar2) is
begin
p(v);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure member(k varchar2, v varchar2) is
begin
p( encap(k) || g_affectation || encap(encode_string(v)));
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure member(k varchar2, n number) is
begin
p( encap(k) || g_affectation || n );
p(g_separation);
end;
--------------------------------------------------------------------------------
--
--------------------------------------------------------------------------------
procedure send is
begin
if (t(i-1) = g_separation) then
t.delete(i-1);
end if;
i := t.first;
while (i is not null) loop
htp.p(t(i));
i := t.next(i);
end loop;
end;
end jsonfly;
/
He empezado a usar esta biblioteca y parece prometedor: https://github.com/pljson/pljson
Fácil de instalar, y los ejemplos son buenos.
Para usar la biblioteca en su ejemplo, agregue estas variables a su procedimiento.
mapData json;
results json_list;
status json_value;
firstResult json;
geometry json;
....
Entonces puedes manipular la respuesta como un objeto json.
-- convert the result from the get to a json object, and show some results.
mapData := json(v_ans);
-- Show the status of the request
status := mapData.get(''status'');
dbms_output.put_line(''Status = '' || status.get_string());
IF (status.get_string() = ''OK'') THEN
results := json_list(mapData.get(''results''));
-- Grab the first item in the list
resultObject := json(results.head);
-- Show the human readable address
dbms_output.put_line(''Address = '' || resultObject.get(''formatted_address'').to_char() );
-- Show the json location data
dbms_output.put_line(''Location = '' || resultObject.get(''geometry'').to_char() );
END IF;
La ejecución de este código lo enviará a la salida de dbms:
Status = OK
Address = "St Paul, MN 55105, USA"
Location = {
"bounds" : {
"northeast" : {
"lat" : 44.9483849,
"lng" : -93.1261959
},
"southwest" : {
"lat" : 44.9223829,
"lng" : -93.200307
}
},
"location" : {
"lat" : 44.9330076,
"lng" : -93.16290629999999
},
"location_type" : "APPROXIMATE",
"viewport" : {
"northeast" : {
"lat" : 44.9483849,
"lng" : -93.1261959
},
"southwest" : {
"lat" : 44.9223829,
"lng" : -93.200307
}
}
}
Oracle 12c ahora tiene soporte nativo de JSON :
La base de datos Oracle admite datos de notación de objetos de JavaScript (JSON) de forma nativa con características de base de datos relacionales, incluidas transacciones, indexación, consulta declarativa y vistas
Los datos JSON y los datos XML se pueden utilizar en Oracle Database de manera similar. A diferencia de los datos relacionales, ambos pueden almacenarse, indexarse y consultarse sin necesidad de un esquema que defina los datos. Oracle Database admite JSON de forma nativa con características de bases de datos relacionales, incluidas transacciones, indexación, consultas declarativas y vistas.
Los datos JSON a menudo se han almacenado en bases de datos NoSQL como Oracle NoSQL Database y Oracle Berkeley DB. Esto permite el almacenamiento y la recuperación de datos que no se basan en ningún esquema, pero no ofrecen los modelos de consistencia rigurosos de las bases de datos relacionales.
Para compensar esta deficiencia, a veces se usa una base de datos relacional en paralelo con una base de datos NoSQL. Las aplicaciones que utilizan datos JSON almacenados en la base de datos NoSQL deben garantizar la integridad de los datos.
El soporte nativo para JSON por Oracle Database obvia tales soluciones provisionales. Ofrece todos los beneficios de las funciones de bases de datos relacionales para su uso con JSON, incluidas las transacciones, la indexación, las consultas declarativas y las vistas.
Las consultas de la base de datos de Oracle son declarativas. Puedes unir datos JSON con datos relacionales. Y puede proyectar datos JSON de manera relacional, haciéndolos disponibles para procesos y herramientas relacionales. También puede consultar, dentro de la base de datos, los datos JSON que se almacenan fuera de la base de datos en una tabla externa.
Puede acceder a los datos JSON almacenados en la base de datos de la misma manera que accede a otros datos de la base de datos, incluido el uso de OCI, .NET y JDBC.
A diferencia de los datos XML, que se almacenan utilizando el tipo de datos SQLType, los datos JSON se almacenan en la base de datos Oracle utilizando los tipos de datos SQL VARCHAR2, CLOB y BLOB. Oracle recomienda que siempre use una restricción de verificación is_json para asegurarse de que los valores de columna sean instancias JSON válidas