parenthesis - json_object oracle 11g

Devuelve los resultados de una consulta SQL como JSON en Oracle 12c

Question

Fondo

Necesito recuperar algunos miles de filas de Oracle y convertirlas a JSON para usarlas en SlickGrid. Actualmente estoy recuperando las filas en PHP, convirtiéndolas de ISO a UTF-8 con iconv y exportando a json con json_encode. Toda la operación toma aproximadamente 1 segundo en el lado de la base de datos y 5 segundos para generar JSON. Es muy largo.

La pregunta

He leído que Oracle 12c es compatible con JSON, pero no puedo encontrar exactamente lo que necesito.

¿Hay alguna manera de devolver el resultado de una consulta de SQL estándar en formato json?

Supuestamente me gustaría emitir una consulta similar a esta:

SELECT * from table AS JSON

y recibe un json válido similar a este:

[{"col1": "value1", "col2": 2}, {"col1": "valueOfRow2", "col2": 3}]

Una cosa importante es que necesito que se me escapen las secuencias de Unicode, ya que uso el juego de caracteres ISO-8859-2 en el lado del cliente, y JSON tiene que estar en UTF-8 o tener las secuencias escapadas.

Answer 1

12cR2 (disponible en Oracle Cloud) admite esto de forma nativa.

SQL> select JSON_ARRAY(EMPLOYEE_ID, FIRST_NAME,LAST_NAME) from HR.EMPLOYEES;

JSON_ARRAY(EMPLOYEE_ID,FIRST_NAME,LAST_NAME)
--------------------------------------------------------------------------------
[100,"Steven","King"]
[101,"Neena","Kochhar"]

o

SQL> select JSON_OBJECT(''ID'' is EMPLOYEE_ID , ''FirstName'' is FIRST_NAME,''LastName'' is LAST_NAME) from HR.EMPLOYEES;

JSON_OBJECT(''ID''ISEMPLOYEE_ID,''FIRSTNAME''ISFIRST_NAME,''LASTNAME''ISLAST_NAME)
----------------------------------------------------------------------------
{"ID":100,"FirstName":"Steven","LastName":"King"}
{"ID":101,"FirstName":"Neena","LastName":"Kochhar"}

Answer 2

El soporte de Oracle 12c para JSON es una capacidad para almacenar objetos JSON, consultarlos y seleccionarlos.

Tiene un formato tabular y solo necesita mostrar sus datos como JSON. Así que simplemente puede concatenar filas en {''col1'': ''rowN1'', ''col2'': ''rowN2''} y hacer el resto en el lado del cliente. O puede usar LISTAGG para obtener todo el documento. Ejemplo: http://technology.amis.nl/2011/06/14/creating-json-document-straight-from-sql-query-using-listagg-and-with-clause/

Solo tenga en cuenta el límite de SQL VARCHAR2 de 4000 caracteres.

También puede consultar http://database-geek.com/2009/03/25/json-in-and-out-of-oracle-json-data-type/ Pero no creo que ese tipo de objeto oracle mejorará tu rendimiento.

Otro enfoque es exportar XML utilizando XMLType. Luego convierte XML a JSON. XMLType se encargará de los caracteres especiales y la API es bastante estable (no tendrá que volver a escribir su programa para Oracle 14).

Answer 3

No veo la solución de Python (en caso de que necesite volcar JSON).

Escribí json-ora-extract para json-ora-extract de tamaño medio (porque el conjunto de datos tiene que caber en la memoria disponible).

Utiliza los wx_Oracle y json Python para leer datos de la base de datos de Oracle (cualquier versión) y *.json archivo *.json .

También hay una opción para crear un archivo comprimido *.gz .

Answer 4

Oracle 12c versión 12.1.0.2 (la última versión a partir del 11.11.2014) agrega soporte JSON: https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#BGBGADCC

Está disponible desde el 17 de octubre. https://blogs.oracle.com/db/entry/oracle_database_12c_release_1

Si no puede parchar / trabajar con esa versión, hay un excelente paquete escrito por Lewis Cunningham y Jonas Krogsboell: PL / JSON * http://pljson.sourceforge.net/

Es un paquete excelente (lo he usado en numerosas instalaciones de bases de datos).

Los ejemplos incluidos son buenos y cubren la mayoría de los escenarios.

declare 
  ret json;
begin
  ret := json_dyn.executeObject(''select * from tab'');
  ret.print;
end;
/

Answer 5

Para agregar a la respuesta en oracle 12.2, puedes crear json como quieras así.

SELECT JSON_ARRAY(
JSON_OBJECT (
         KEY ''number'' VALUE s.number,
         KEY ''name'' VALUE s.sname,
         KEY ''location'' VALUE s.loc
          )
       ) AS student_det
FROM   student s;

Answer 6

Puede usar el xmltype para convertir el resultado de un SQL en XML y JSON. Consulte el siguiente artículo para la solución que funcionará para Oracle desde la versión 9. También puede descargar el paquete itstar_xml_util:

http://stefan-armbruster.com/index.php/12-it/pl-sql/12-oracle-xml-and-json-goodies

Un ejemplo simple con la tabla emp:

declare
  l_sql_string varchar2(2000);
  l_xml        xmltype;
  l_json       xmltype;
begin
  l_sql_string := ''select a.empno, a.ename, a.job from emp a'';

  -- Create the XML aus SQL
  l_xml := itstar_xml_util.sql2xml(l_sql_string);

  -- Display the XML
  dbms_output.put_line(l_xml.getclobval());

  l_json := itstar_xml_util.xml2json(l_xml);
  -- Display the JSON
  dbms_output.put_line(l_json.getclobval());  
end;

El resultado se ve así:

{"ROWSET": [
    {
      "EMPNO": 7839,
      "ENAME": "KING",
      "JOB": "PRESIDENT"
    },
    {
      "EMPNO": 7698,
      "ENAME": "BLAKE",
      "JOB": "MANAGER"
    },
[...]
    {
      "EMPNO": 7934,
      "ENAME": "MILLER",
      "JOB": "CLERK"
    }
  ]}

Answer 7

Solo prueba esto:

:) la vida es feliz

with data as
  ( select 
    xmlelement(e,regexp_replace(''{"name":"''||colname||''"}'', ''[[:cntrl:]]'', ''''),'','') col1
    from tblname
  )
  select
        rtrim(replace(replace(replace(xmlagg(col1).getclobval(),''&''||''quot;'',''"''),''<E>'',''''),''</E>'',''''),'','')
        as very_long_json
  from data;