Oracle 19c - Otras Mejoras en el manejo de JSON

Soporte GeoJSON

Oracle 19c permite usar “JSON_VALUE” para devolver objetos de tipo “SDO_GOMETRY” a partir de documentos JSON que poseen información en formato GeoJSON. La sintaxis es la siguiente:

SELECT JSON_VALUE(data, '$.features[0].geometry'

                  RETURNING SDO_GEOMETRY

                  ERROR ON ERROR)

FROM json_documents;

Y da como resultado:

JSON_VALUE(DATA,'$.FEATURES[0].GEOMETRY'RETURNINGSDO_GEOMETRYERRORONERROR)(SDO_GTYPE, SDO_SRID, SDO_

----------------------------------------------------------------------------------------------------

SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(-.15380859, 51.5053234, NULL), NULL, NULL)

Y se pueden crear índices en los mismos

CREATE INDEX json_documents_geo_idx

  ON json_documents (JSON_VALUE(data, '$.features[0].geometry'

                     RETURNING SDO_GEOMETRY))

  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Mapeo JSON / SQL Object Type

Oracle 19c permite usar “JSON_VALUE” para devolver objetos de tipo SQL definido por el usuario, a partir de documentos JSON:

Ejemplo:

CREATE TABLE DocumentoJSON ( 

      ID_DocumentoJSON NUMBER(10) NOT NULL, 

      Datos CLOB, 

      CONSTRAINT PK_DocumentoJSON PRIMARY KEY (ID_DocumentoJSON),

      CONSTRAINT CK_DocumentoJSON_DatosJSON CHECK(Datos IS JSON)

);

INSERT INTO DocumentoJSON 

 VALUES (1, '{"marca":"Ford", "modelo":"Mustang", "cantidad":3}');

INSERT INTO DocumentoJSON 

 VALUES (2, '{"marca":"Chevrolet", "modelo":"Camaro", "cantidad":5}');

INSERT INTO DocumentoJSON 

 VALUES (3, '{"marca":"Dodge", "modelo":"Charger", "cantidad":1}');

--

COMMIT;

--

CREATE OR REPLACE TYPE ut_Auto AS OBJECT (

  MARCA VARCHAR2(100),

  MODELO VARCHAR2(100),

  CANTIDAD NUMBER(5));

/

--

SELECT JSON_VALUE(Datos, '$' RETURNING ut_Auto) AS Auto

FROM DocumentoJSON

WHERE ID_DocumentoJSON = 1;

El resultado que obtendremos es:

AUTO(MARCA, MODELO, CANTIDAD)

-----------------------------------------------------------------------

UT_AUTO(FORD, MUSTANG,3)

La cláusula "ON MISMATCH" permite generar un error, devolver el mismo o devolver nulo cuando alguno de los elementos del documento JSON no coincide con la estructura del objeto. La sintaxis de la misma es:

JSON_value_on_mismatch ( ( IGNORE | ERROR | NULL ) 

    ON MISMATCH  [  ( (MISSING DATA) | (EXTRA DATA) | (TYPE ERROR) )  ]

  )...

Se puede realizar este mismo tipo de operación con tablas (NESTED TABLES). Y se puede realizar la operación inversa, convertir un objeto SQL en JSON.

Cláusula SQL NESTED

Cuando usamos la función JSON_TABLE, Oracle internamente realiza un INNER JOIN entre la tabla origen y el resultado de la función. Por lo tanto, si una fila de la tabla no posee datos JSON, la misma no es visualizada.

Una forma de solucionar el problema hasta Oracle 19c era realizar un LEFT JOIN, como vemos en el siguiente ejemplo:

SELECT j.id, jt.marca, jt.modelo

FROM   DocumentoJSON j LEFT OUTER JOIN JSON_TABLE(j.Datos, '$'

         COLUMNS (marca    VARCHAR2(50 CHAR)  PATH marca,

                  modelo   VARCHAR2(50 CHAR)  PATH modelo)) jt ON 1=1;

Esto puede reemplazarse en Oracle 19c por la más legible cláusula “NESTED”:

SELECT j.id, jt.marca, jt.modelo

FROM   DocumentoJSON j NESTED datos

         COLUMNS (marca    VARCHAR2(50 CHAR)  PATH marca,

                  modelo   VARCHAR2(50 CHAR)  PATH modelo) jt;