Skip navigation

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;

 

 

 

 

Oracle 18c permite usar “JSON_TABLE” en vistas materializadas con la opción de refresco “ON STATEMENT”.

 

Oracle 19c permite que estas vistas sean utilizadas por consultas que ejecuten funciones “JSON_VALUE” o “JSON_EXISTS” utilizando la funcionalidad de Query Rewrite, lo cual no era posible previamente.

 

 

 

Para esto, la vista debe cumplir con algunas condiciones en particular:

 

  • La vista materializada debe crearse con “REFRESH FAST ON STATEMENT” e incluir el ROWID o la clave primaria.
  • La vista materializada solo puede ser un JOIN entre la tabla maestra y "JSON_TABLE".
  • Solo las columnas de "JSON_TABLE" definidas como “ERROR ON ERROR NULL ON EMPTY” se consideran para reescribir.
  • La funcionalidad admite notación de puntos, llamadas a "JSON_VALUE" y "JSON_EXISTS", que pueden reescribirse con llamadas a JSON_TABLE y, por lo tanto, son aplicables para una re-escritura para usar la vista materializada.

 

Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar las mejoras introducidas a la función JSON_OBJECT.

 

En los siguientes ejemplos, vamos a utilizar una tabla llamada Producto que podemos crear con el script incluido a continuación:

 

CREATE TABLE Producto (

      ID NUMBER(10) NOT NULL,

      Marca VARCHAR(100) NOT NULL,

      Nombre VARCHAR(100) NOT NULL,

      Precio NUMBER(10,4) NOT NULL,

      CONSTRAINT PK_Producto PRIMARY KEY (ID)

);

INSERT INTO Producto VALUES (1, 'Sony', 'TV LED 40"', 20000);

INSERT INTO Producto VALUES (2, 'Philips', 'TV LED 32"', 15000 );

INSERT INTO Producto VALUES (3, 'Motorola', 'Moto Z4', 25000);

--

COMMIT;

 

 

Uso de Comodín

Es posible utilizar el comodín “*” para referenciar todas las columnas en un solo paso. Se puede usar el mismo para toda la consulta o para una tabla en particular usando un alias:

 

SELECT JSON_OBJECT(t.*) AS json_data FROM <Tabla> t;

 

Ejemplo

SELECT JSON_OBJECT(p.*) AS json_data FROM Producto p;

 

 

Lista de Columnas

Es posible utilizar una lista de columnas separadas por coma. Los nombres de los elementos mantienen las mayúsculas / minúsculas definidas en la lista:

 

SELECT JSON_OBJECT(columnaA, ColumnaB) AS json_data

FROM <Tabla> t;

 

 

Ejemplo

 

SELECT JSON_OBJECT(Nombre, Precio) AS json_data

FROM Producto p;

 

 

 

Sintaxis KEY ... VALUE simplificada

No es necesario utilizar la sintaxis ‘KEY … VALUE’, pudiéndose usar directamente “:” para definir los pares de datos:

 

SELECT JSON_OBJECT('ID' : id,

                   'Nombre' : name) AS json_data

FROM persons;

 

Ejemplo

 

SELECT JSON_OBJECT('ID':id,

                   'Producto':nombre) AS json_data_new

FROM Producto p;

 

 

 

Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar el funcionamiento de la función JSON_SERIALIZE.

 

La misma nos permite convertir un documento JSON, cualquiera sea su formato (BLOB, RAW, CLOB) en texto

 

La sintaxis de la función es la siguiente:

 

JSON_SERIALIZE (target_expr [ json_query_returning_clause ] [ PRETTY ]

                 [ ASCII ] [ TRUNCATE ] [ json_query_on_error_clause ])

 

  • La cláusula "RETURNING" así como las opciones “PRETTY”, “ASCII” y “TRUNCATE” se comportan igual a lo visto en la función JSON_MERGEPATCH.
  • La función JSON_SERIALIZE permite el uso de la clausula “ON ERROR” para definir que comportamiento tomar en caso de que encuentre un error al ejecutarse.
  • Al igual que la función “TREAT(… AS JSON)” y "JSON_ MERGEPATCH", es una función SQL que no puede emplearse en forma directa en PL/SQL.

 

Ejemplos de Uso:

Primero, vamos a crear una tabla conteniendo algunos documentos JSON sencillos en una columna BLOB:

CREATE TABLE BLOBJSON (

       ID NUMBER(10) NOT NULL,

       Datos BLOB,

       CONSTRAINT PK_BLOBJSON PRIMARY KEY (ID),

       CONSTRAINT CK_BLOBJSON_DatosJSON CHECK(Datos IS JSON)

);

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

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

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

--

COMMIT;

Si queremos consultar los datos, no podemos hacerlo ya que se encuentran en formato binario:

 

 

Si los datos no son muy grandes, se puede usar la función "UTL_RAW.cast_to_varchar2"para realizar la conversion:

 

SELECT UTL_RAW.cast_to_varchar2(Datos) AS data2 FROM BLOBJSON;

 

 

Y el resultado que obtenemos es el siguiente:

 

 

Otra opción es utilizar la función JSON_QUERY para ver el contenido:

 

SELECT JSON_QUERY(Datos, '$') AS data FROM BLOBJSON;

 

 

Y el resultado que obtenemos es el siguiente:

 

O directamente utilizar la nueva función (en este caso, adicionalmente estamos filtrando la consulta para ver solo un registro):

 

SELECT JSON_SERIALIZE(d.Datos PRETTY) AS data

FROM   BLOBJSON d

WHERE  d.datos.marca = 'Dodge';

 

 

Y el resultado que obtenemos es el siguiente:

 

 

Oracle 19c introduce algunas mejoras significativas en el manejo de datos en formato JSON. En este artículo en particular voy a explicar el funcionamiento de la función JSON_MERGEPATCH.

 

En Oracle 12c y 18c para modificar un documento JSON era necesario obtenerlo, procesar y aplicar los cambios y luego reemplazar el documento original con la versión modificada. Con “JSON_MERGEPATCH” es posible simplificar el proceso notablemente en algunos casos, ya que esta función permite modificar el contenido de un objeto JSON, ya sea en una sentencia SELECT como en un UPDATE.

 

La sintaxis de la función es la siguiente:

 

JSON_MERGEPATCH

   ( target_expr, patch_expr[returning_clause] [PRETTY] [ASCII]

     [TRUNCATE] [on_error_clause] )

 

Si “patch_expr” se refiere a un elemento existente (o a un grupo de elementos), el o los mismos son actualizados con el valor provisto en la expresión, al cual puede ser por ejemplo el valor en blanco.

  • Si “patch_expr” no se refiere a un elemento existente, el mismo es agregado a cada documento.
  • Si “patch_expr” le asigna el valor nulo a un elemento, el mismo es removido de los documentos.
  • La clausula "RETURNING" funciona como en todas las otras funciones de manejo de JSON, y permite definir un tipo de dato.
  • La opción "PRETTY" muestra el resultado con un formato mas legible, aunque mas extenso.
  • La opción "TRUNCATE" indica que el resultado debe ser truncado si es mayor que el tipo de datos definido.
  • La opción "ASCII" convierte cualquier carácter no-ASCII a una secuencia de escape JSON.
  • La función JSON_MERGEPATCH permite el uso de la clausula “ON ERROR” para definir que comportamiento tomar en caso de que encuentre un error al ejecutarse.

 

Al igual que la función “TREAT(… AS JSON)”, es una función SQL que no puede emplearse en forma directa en PL/SQL. Para hacerlo es necesario embeber la misma en una llamada a SQL, por ejemplo "SELECT JSON_MERGEPATCH(...) INTO xxx FROM DUAL".

 

La función JSON_MERGEPATCH se puede utilizar tanto en un SELECT como en sentencias UPDATE para actualizar los datos contenidos en un documento JSON.

 

Ejemplos de Uso:

Primero, vamos a crear una tabla conteniendo algunos documentos JSON sencillos:

 

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;

 

A continuación vamos a consultar los datos, pero reemplazando el elemento "cantidad" con el valor 500:

 

SELECT JSON_MERGEPATCH(Datos, '{"cantidad":500}') AS DatosModificados

FROM   DocumentoJSON;

 

Y el resultado que obtenemos es el siguiente:

 

 

A continuación vamos a consultar nuevamente los datos, pero agregando el elemento "Marca" (recuerden que "marca" ya existe, pero  JSON es case sensitive en cuanto a la definición de los nombres de elementos por lo que lo considera un nuevo elemento):

 

SELECT JSON_MERGEPATCH(Datos, '{"Marca":"Skoda"}') AS DatosModificados
FROM   DocumentoJSON;

Y el resultado que obtenemos es el siguiente (noten que la cantidad se mantiene tal cual la insertamos originalmente, ya que la función aplicada en el ejemplo anterior fue en una sentencia SELECT y por lo tanto no modificó los datos):

 

 

 

En este ejemplo, vamos a remover el elemento "marca", asignándole el valor NULL:

 

SELECT JSON_MERGEPATCH(Datos, '{"marca":NULL}' PRETTY) AS DatosModificados

FROM   DocumentoJSON;

 

Y el resultado que obtenemos es el siguiente:

 

 

Como ultimo ejemplo, vamos a actualizar los datos en la tabla, agregando el elemento "importado", y luego consultamos los datos tal cual están guardados en la base de datos:

 

UPDATE DocumentoJSON dj

SET Datos = JSON_MERGEPATCH(Datos, '{"importado":"USA"}') ;

--

SELECT * FROM DocumentoJSON;

 

Y el resultado que obtenemos es el siguiente: