Oracle 23c & JSON - Relational Duality Views - Parte #3

En este artículo final de la serie de JSON Relational Duality Views, vamos a analizar las ventajas y casos de uso que nos ofrece esta nueva funcionalidad de Oracle Database 23c.

Algunas Ventajas de JSON Relational Duality Views

Alta Concurrencia

JSON Relational Duality Views introducen control de concurrencia sin bloqueo previo (concurrencia optimista), basado en versiones que contempla la actualización de datos tanto mediante el UPDATE de un documento JSON como la ejecución de sentencias UPDATE en forma directa a las tablas relacionales involucradas.

Eliminación de Datos Duplicados

Las bases de datos de documentos tienen (en general) el problema de mantener sus documentos independientes. Al no tener un buen mecanismo para declarar relaciones entre documentos, que genera duplicación de datos e inconsistencias. Los datos duplicados conducen a mayores costos de mantenimiento, errores en la toma de decisiones y oportunidades comerciales perdidas.

JSON Relational Duality elimina la duplicación de datos ya que la información de un único documento JSON es almacenada mediante transacciones ACID (atomicidad, consistencia, aislamiento, durabilidad) en diversas tablas relacionales, eliminando la duplicación de datos pero manteniendo la consistencia automáticamente.

Datos Accesibles en Múltiples Documentos

Las bases de datos de documentos no son flexibles cuando debemos tener información compartida entre distintas jerarquías. Si deseamos tener información de los productos en los documentos de Compras y Ventas, es probable que tengamos que duplicar la misma en ambas jerarquías, o que debamos usar una tercer jerarquía que se relacione a ambas pero sacrificando la integridad y generando un modelo mixto (documentos relacionados) que suma mas problemas que lo que soluciona.

Las vistas duales JSON Relational permiten definir multiples múltiples vistas de JSON en grupos de tablas superpuestas. Este modelado de datos flexible hace que la creación de múltiples aplicaciones con los mismos datos sea fácil y eficiente, permitiendo por ejemplo que la tabla relacional de productos sea incluida tanto en la vista de Compras como la de Ventas, siendo actualizable en ambas pero almacenada en forma única y consistente.

Eliminar Roundtrips

Al desarrollar una aplicación es necesario mapear entre objetos de nivel de aplicación (como clases, utilizados por lenguajes de programación) y las tablas relacionales. Es normal tener que modificar datos de varias tablas para implementar un caso de uso empresarial, lo cual genera un problema llamado "desajuste de impedancia relacional de objetos". Es frecuente el uso de frameworks (Object Relational Mapping - ORM) que facilitan las tareas de mapeo, pero en general es a expensas de performance ya que no utilizan las funciones avanzadas que ofrecen las bases de datos. Dado que una operación en un objeto de nivel de aplicación puede dar lugar a múltiples accesos o actualizaciones de las tablas participantes, esto ralentiza el rendimiento de la aplicación.

JSON Relational Duality Views elimina la necesidad de asignar objetos a tablas relacionales a nivel de la aplicación, proporcionando vistas JSON totalmente actualizables sobre los datos almacenados en forma relacional. Las aplicaciones pueden leer un documento, realizar los cambios necesarios y volver a escribir el documento sin preocuparse por la estructura de datos subyacente, el mapeo, la consistencia o el ajuste del rendimiento, permitiendo recuperar o almacenar todos los datos necesarios para una aplicación en una sola operación de base de datos.

Lo Mejor de Ambos Mundos

En definitiva, con JSON Relational Duality Views podemos unir las ventajas de ambos mundos!

JSON Relational Duality Views permite:

• Eliminar el uso de frameworks de ORM para mapear información de las aplicaciones al modelo de datos relacional que la almacena.
• Acceder a la información relacional mediante APIs de documentos como ser Oracle REST Data Services u Oracle Database API for MongoDB.

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle 23c & JSON - Relational Duality Views - Parte #2

En este segundo artículo sobre JSON Relational Duality Views vamos a analizar algunas características mas avanzadas de las mismas y vamos a analizar también las vistas del diccionario de datos que contienen información sobre esta nueva funcionalidad.

Control de Concurrencia Optimista

En el articulo anterior "Oracle 23c & JSON - Relational Duality Views - Parte #1" estuvimos actualizando documentos JSON mediante el uso de vistas duales, pero no contemplamos la posibilidad que los datos subyacentes hayan cambiado, algo que en la vida real ocurre frecuentemente.

Duality Views utiliza una nueva arquitectura de control de concurrencia optimista o sin bloqueo disponible en Oracle Database 23c que permite a los desarrolladores administrar sus datos de manera consistente en operaciones sin estado (como ser REST GET y PUT), ya que los bloqueos tienen estado y no se pueden retener en llamadas sin estado.

Este nuevo algoritmo posee las siguientes características:

• Incorpora el concepto de etiqueta de entidad (ETAG) del protocolo HTTP a la base de datos, siendo una ETAG un hash o una firma para el contenido de una página web recuperada, pero esto aplicado a documentos.
• Cuando se realiza un GET en Duality View, el documento JSON devuelto también contiene el ETAG del conjunto de filas utilizadas en la construcción del documento.
• Cuando ese documento se modifica y luego se vuelve a poner en la base de datos, el ETAG proporcionado se compara con el ETAG actual de las filas. Si el ETAG difiere, el objeto debe haber sido modificado por otra sesión entre el momento en que se hizo el GET y el momento en que se envió el PUT, por lo que el PUT es rechazado. La aplicación puede volver a obtener la versión mas actualizada del documento (el cual contiene un nuevo ETAG) mediante un GET, aplicarle los cambios requeridos, y volver a intentar hacer un PUT.
• Si el PUT tiene éxito, tenemos la garantía de que no se han producido cambios intermedios en el objeto y garantizamos la atomicidad y la coherencia a nivel de documento.

Este tipo de control de concurrencia sin bloqueo permite a los desarrolladores centrarse en su aplicación sin tener que implementar control de concurrencia dentro de la aplicación.

Control de Concurrencia Optimista en Funcionamiento

Vamos a ver como funciona el control de concurrencia en la práctica. Lo primero que vamos a hacer es consultar un documento en particular, usando los ejemplos del artículo "Oracle 23c & JSON - Relational Duality Views - Parte #1" donde comenzamos a explicar como funcionan las vistas duales.

Vamos a obtener el documento JSON asociado al comprobante #3, usando la siguiente sintaxis:

SELECT JSON_SERIALIZE(c.data PRETTY)
FROM VDCOMPROBANTE c
WHERE c.data.IDComprobante = 3;

Lo cual devuelve el siguiente documento:

JSON_SERIALIZE(C.DATAPRETTY)                                         
---------------------------------------------------------------------
{
  "_metadata" :
  {
    "etag" : "66C4365F9A178623F27913B947DF8C7C",
    "asof" : "00000000007844AF"
  },
  "IDComprobante" : 3,
  "FechaComprobante" : "2023-05-16T00:00:00",
  "IDCliente" : 3,
  "Importe" : 14200,
  "Detalles" :
  [
    {
      "IDDetalle" : 5,
      "IDProducto" : 5,
      "CantidadProducto" : 14,
      "ImporteUnitario" : 1000
    }
  ]
}

El valor resaltado en ROJO es el ETAG calculado para la versión actual del documento, el cual sirve para identificar a la misma.

A continuación, vamos a usar una sentencia UPDATE de SQL para actualizar en forma directa, en la tabla de detalles de comprobantes, el valor de la columna cantidad del registro de detalle #5, como vemos a continuación:

UPDATE tDetalleComprobante
SET Cantidad = 7
WHERE ID_DetalleComprobante = 5;

1 row updated.

Volviendo al documento JSON que obtuvimos en el primer SELECT, asumamos ahora que la aplicación que lo obtuvo le hizo una modificación (por ejemplo, cambió el ID de cliente, como remarcamos en azul) y desea grabar dicho cambio, lo cual hace enviando el documento completo (incluyendo el ETAG que obtuvo al leer en documento) ejecutando la siguiente sentencia:

UPDATE vdComprobante c
SET data = 
'{
  "_metadata" :
  {
    "etag" : "66C4365F9A178623F27913B947DF8C7C",
    "asof" : "00000000007844AF"
  },
  "IDComprobante" : 3,
  "FechaComprobante" : "2023-05-16T00:00:00",
  "IDCliente" : 500,
  "Importe" : 14200,
  "Detalles" :
  [
    {
      "IDDetalle" : 5,
      "IDProducto" : 5,
      "CantidadProducto" : 14,
      "ImporteUnitario" : 1000
    }
  ]
}'
WHERE c.data.IDComprobante = 3;

Al ejecutarla, obtendremos el siguiente mensaje de error:

Error report -
ORA-42699: Cannot update JSON Relational Duality View 'VDCOMPROBANTE': The ETAG of document with ID 'FB03C10400' in the database did not match the ETAG passed in.

Esto se debe a que el ETAG del documento almacenado en la base de datos cambió al momento en que modificamos el detalle, lo cual podemos ver si volvemos a consultar el mismo:

SELECT JSON_SERIALIZE(c.data PRETTY)
FROM VDCOMPROBANTE c
WHERE c.data.IDComprobante = 3;

Obteniendo:

JSON_SERIALIZE(C.DATAPRETTY)                                    
----------------------------------------------------------------
{
  "_metadata" :
  {
    "etag" : "5A94261D02F1232A3F126D7C36A961BD",
    "asof" : "00000000007848C6"
  },
  "IDComprobante" : 3,
  "FechaComprobante" : "2023-05-16T00:00:00",
  "IDCliente" : 3,
  "Importe" : 14200,
  "Detalles" :
  [
    {
      "IDDetalle" : 5,
      "IDProducto" : 5,
      "CantidadProducto" : 7,
      "ImporteUnitario" : 1000
    }
  ]
}

Con la versión "actualizada" del documento, la aplicación tiene que volver a aplicar el cambio individual deseado (actualizar el cliente) y volver a enviar todo el documento, con el ETAG que ahora si coincide con la versión almacenada en la base de datos.

Vistas del Diccionario de Datos

El diccionario de datos de Oracle 23 incorpora algunas nuevas vistas para acceder a la información de las vistas duales JSON Relacional. Veremos las mismas en detalle a continuación (analizaremos las visas que comienzan con USER_, que permiten acceder a los objetos del usuario conectado, existen versiones con prefijo ALL_ y DBA_ que ven todos los objetos sobre los que el usuario posee permisos y todos los objetos de la base de datos respectivamente).

USER_JSON_DUALITY_VIEWS

Esta vista contiene información general de cada JSON Relational Duality View definida en el esquema.

SELECT * FROM USER_JSON_DUALITY_VIEWS;

Las columnas ALLOW_INSERT, ALLOW_UPDATE y ALLOW_DELETE nos indican que tipo de acciones se pueden realizar sobre las mismas. La columna JSON_SCHEMA contiene el esquema JSON que describe al documento que esta disponible al utilizar cada una de las vistas.

USER_JSON_DUALITY_VIEW_TABS

Esta vista nos muestra la tabla principal y todas las tablas involucradas en cada una de las vistas duales:

SELECT * FROM USER_JSON_DUALITY_VIEW_TABS;

USER_JSON_DUALITY_VIEW_TAB_COLS

Esta vista muestra información sobre todas las columnas (atributos del documento JSON) definidas en una vista dual:

SELECT * FROM USER_JSON_DUALITY_VIEW_TAB_COLS;

La columna JSON_KEY_NAME nos indica el nombre de atributo en el documento JSON y las columnas PRIMARY_KEY_POS y ETAG_POS nos indica si forma parte de la clave primaria (y en que posición) y en que posición se encuentra dentro del ETAG.

USER_JSON_DUALITY_VIEW_LINKS

Esta vista nos permite entender las relaciones entre las distintas tablas en cada vista / documento JSON:

SELECT * FROM USER_JSON_DUALITY_VIEW_LINKS;

Conclusión

En este artículo hicimos un análisis del mecanismo de control de concurrencia optimista (sin lockeos) implementado en JSON Relational Duality Views y las vistas del diccionario de datos que contienen información

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle 23c & JSON - Relational Duality Views - Parte #1

La version 23c de la base de datos Oracle introduce muchas mejoras para trabajar con documentos JSON, en este artículo vamos a comenzar por una de las mas importantes, que es la disponibilidad de vistas "duales" para ver e interactuar con la información almacenada en nuestra base de datos en tablas relacionales pero directamente en formato JSON.

En el artículo vamos a utilizar dos tablas las cuales pueden crear para probar esta nueva funcionalidad usando el código a continuación:

CREATE TABLE tComprobante (
    ID_Comprobante NUMBER(10) CONSTRAINT PK_Comprobante PRIMARY KEY,
    Fecha_Comprobante DATE NOT NULL,
    ID_Cliente NUMBER(10) NOT NULL,
    Importe DECIMAL (10,2) NOT  NULL);
--
CREATE TABLE tDetalleComprobante (
    ID_DetalleComprobante NUMBER(10) 
        CONSTRAINT PK_DetalleComprobante PRIMARY KEY,
    ID_Comprobante NUMBER(10) NOT NULL 
        CONSTRAINT FK_DetalleComprobante_ID_Comprobante REFERENCES tComprobante(ID_Comprobante),
    ID_Producto NUMBER(10) NOT NULL,
    Cantidad NUMBER(10) NOT NULL,
    ImporteUnitario DECIMAL (10,2) NOT  NULL);

Y también vamos a insertar algunos datos en las mismas, de la siguiente manera (aprovechando la nueva funcionalidad "Oracle 23c - Constructor de Tabla a partir de Valores"):

INSERT INTO tComprobante
VALUES (1, DATE '2023-05-15', 1, 20500),
       (2, DATE '2023-05-15', 2, 9800),
       (3, DATE '2023-05-16', 3, 14200);
--
INSERT INTO tDetalleComprobante
VALUES (1, 1, 1, 20, 1000),
       (2, 1, 2, 5, 100),
       (3, 2, 3, 9, 1000),
       (4, 2, 4, 8, 100),
       (5, 3, 5, 14, 1000),
       (6, 3, 6, 2, 100);
--
COMMIT;

Creando una Vista Dual JSON - Relacional

Vamos a crear una vista dual, la cual nos permite ver la información de varias tablas de nuestro sistema como si fuera un objeto JSON, usando la siguiente sintaxis:

CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW vdComprobante AS
SELECT JSON {'IDComprobante' : c.ID_Comprobante,
             'FechaComprobante' : c.Fecha_Comprobante,
             'IDCliente' : c.ID_Cliente,
             'Importe' : c.Importe ,
             'Detalles' : [ SELECT JSON {'IDDetalle' : d.ID_DetalleComprobante,
                              'IDProducto' : d.ID_Producto,
                              'CantidadProducto' : d.Cantidad,
                              'ImporteUnitario' : d.ImporteUnitario}
                 FROM tDetalleComprobante d WITH INSERT UPDATE DELETE
                 WHERE d.ID_Comprobante = c.ID_Comprobante ]
            } 
FROM tComprobante c WITH INSERT UPDATE DELETE;

La sintaxis es sencilla, en el ejemplo, estamos definiendo el mapeo entre las columnas de nuestras dos tablas (en verde) y los atributos del documento JSON (en naranja), y estamos permitiendo insertar, eliminar y actualizar registros de ambas tablas mediante la vista.

Usando una Vista Dual JSON - Relacional

Consultar Datos

Una vez que creamos la vista, podemos comenzar a usarla. Lo primero que vamos a hacer es consultar los datos disponibles en ella usando una sentencia SELECT:

SELECT * FROM vdComprobante;

Vemos que la vista posee una única columna, llamada DATA, la cual contiene un documento JSON representando a cada registro de nuestra tabla tComprobante y sus detalles asociados:

Tambien podemos usar la función JSON_SERIALIZE para obtener una representación mas "legible" del documento, usando la opción PRETTY de la misma, como mostramos a continuación:

SELECT JSON_SERIALIZE(c.data PRETTY) FROM vdComprobante c;

Nota: vemos que el documento contiene un elemento adicional llamado "_metadata" que vamos a explicar y analizar en un segundo artículo sobre JSON Relational Duality Views.

Podemos ver que la vista posee una única columna de tipo JSON si usamos el comando DESC para describir a la misma:

DESC vdComprobante

Name Null? Type
---- ----- ----
DATA       JSON

Actualizar Datos

Las vistas duales no solo permiten ver los datos de nuestro modelo relacional como objetos JSON, sino que también podemos utilizarlas para actualizar datos tanto de la entidad principal (Comprobante) como de sus detalles usando un documento JSON. Probaremos de modificar la fecha del primer comprobante, y la cantidad y precio del primer item de dicho comprobante (nuevos valores resaltados en ROJO) , como vemos a continuación:

UPDATE vdComprobante c
SET c.data = (
'{
  "IDComprobante" : 1,
  "FechaComprobante" : "2023-05-14T00:00:00",
  "IDCliente" : 1,
  "Importe" : 20500,
  "Detalles" :
  [
    {
      "IDDetalle" : 1,
      "IDProducto" : 1,
      "CantidadProducto" : 10,
      "ImporteUnitario" : 2000
    },
    {
      "IDDetalle" : 2,
      "IDProducto" : 2,
      "CantidadProducto" : 5,
      "ImporteUnitario" : 100
    }
  ]
}')
WHERE c.data.IDComprobante = 1;

Nota: Usamos "dot notation" para referirnos a los elementos dentro del documento JSON almacenado en la columna data desde la sentencia SQL.

Tras ejecutar ese update sobre la vista, podemos ver que la fecha del comprobante fue actualizada y se ve reflejada así tanto en la table como en la vista dual:

SELECT ID_Comprobante, Fecha_Comprobante
FROM tComprobante c
WHERE c.ID_Comprobante = 1;

ID_COMPROBANTE FECHA_COMPROBANTE  
-------------- -------------------
             1 14-05-2023 00:00:00

SELECT c.data.IDComprobante, c.data.FechaComprobante
FROM vdComprobante c
WHERE c.data.IDComprobante = 1;

IDCOMPROBANTE                                          FECHACOMPROBANTE                                      
------------------------------------------------------ ------------------------------------------------------
1                                                      "2023-05-14T00:00:00"                                 

Lo mismo con las cantidades y precios del primer detalle:

SELECT d.ID_DetalleComprobante, d.Cantidad, d.ImporteUnitario
FROM tDetalleComprobante d
WHERE d.ID_DetalleComprobante = 1;


ID_DETALLECOMPROBANTE   CANTIDAD IMPORTEUNITARIO
--------------------- ---------- ---------------
                    1         10            2000

Eliminar Datos

Podemos eliminar datos usando directamente la vista, como se muestra en el ejemplo:

DELETE FROM vdComprobante c
WHERE c.data.IDComprobante = 3;

1 row deleted.

La funcionalidad de DELETE sirve para eliminar todo el documento, si quisiéramos eliminar solamente un detalle de un comprobante tenemos que hacer un UPDATE sobre la vista enviando el documento JSON sin el registro de detalle que se desea eliminar, como podemos ver a continuación (previo a restaurar el comprobante #3 que eliminamos recién):

UPDATE vdComprobante c
SET c.data = (
'{
  "IDComprobante" : 3,
  "FechaComprobante" : "2023-05-16T00:00:00",
  "IDCliente" : 3,
  "Importe" : 14200,
  "Detalles" :
  [
    {
      "IDDetalle" : 5,
      "IDProducto" : 5,
      "CantidadProducto" : 14,
      "ImporteUnitario" : 1000
    }
  ]
}')
WHERE c.data.IDComprobante = 3;

Podemos validar que ahora el comprobante #3 posee un único item de la siguiente manera:

SELECT ID_DetalleComprobante, Cantidad, ImporteUnitario, ID_Producto
FROM tDetalleComprobante d
WHERE d.ID_Comprobante = 3;

ID_DETALLECOMPROBANTE   CANTIDAD IMPORTEUNITARIO ID_PRODUCTO
--------------------- ---------- --------------- -----------
                    5         14            1000           5

Insertar Datos

Así como pudimos actualizar y eliminar, podemos insertar nuevos datos mediante las vistas duales. Si queremos insertar un detalle, en realidad debemos actualizar el documento con un detalle adicional, pero si queremos insertar un nuevo documento compuesto por un Comprobante y su detalle, usamos la sentencia SELECT como mostramos a continuación, y estaremos insertando datos en ambas tablas:

INSERT INTO vdComprobante c
VALUES (
'{
  "IDComprobante" : 4,
  "FechaComprobante" : "2023-05-16T00:00:00",
  "IDCliente" : 4,
  "Importe" : 6500,
  "Detalles" :
  [
    {
      "IDDetalle" : 7,
      "IDProducto" : 7,
      "CantidadProducto" : 6,
      "ImporteUnitario" : 1000
    }
  ]
}');

Podemos validar el resultado de dicha operación con las siguientes consultas:

SELECT ID_Comprobante, Fecha_Comprobante
FROM tComprobante c
WHERE c.ID_Comprobante = 4;

ID_COMPROBANTE FECHA_COMPROBANTE  
-------------- -------------------
             4 16-05-2023 00:00:00


SELECT ID_DetalleComprobante, Cantidad, ImporteUnitario, ID_Producto
FROM tDetalleComprobante d
WHERE d.ID_Comprobante = 4;

ID_DETALLECOMPROBANTE   CANTIDAD IMPORTEUNITARIO ID_PRODUCTO
--------------------- ---------- --------------- -----------
                    7          6            1000           7

Conclusión

En este artículo hicimos un análisis inicial de la funcionalidad de JSON - Relational Duality Views que seguiremos explorando en los próximos días, así como otras importantes mejoras a JSON disponibles en Oracle Database 23c 

Si desean conocer más sobre Oracle 23c, es recomendable que vean estos artículos en este blog como punto de partida:

• Oracle Database 23c Free - Developer Edition.
• Instalando Oracle Database 23c (Free Developer Edition) en Oracle Cloud.

Adicionalmente, pueden consultar todos los artículos relacionados a Oracle Database 23c agrupados en en el tag Database 23c.

Oracle Cloud Infrastructure - Creando una Base de Datos JSON Autónoma

El anuncio del jueves 13/08 sobre Oracle Autonomous JSON Database (sobre el cual informé en este artículo) generó mucho interés en la comunidad de base de datos Oracle, ya que amplía las dos ofertas existentes (Autonomous Transaction Processing y Autonomous Data Warehouse) con un nuevo tipo de almacenamiento optimizado para documentos JSON.

A continuación vamos a ver los pasos requeridos para crear una base de datos autónoma JSON y conectarnos a la misma.

Creando una Base de Datos JSON Autónoma

El primer paso es navegar hasta las opción de "Autonomous JSON Database", dentro de la sección "Oracle Database" del menú principal de la consola de Oracle Cloud:

En la ventana siguiente, debemos verificar en qué compartimiento queremos crear la base de datos, y a continuación seleccionar la opción "Create Autonomous Database" que nos lleva al asistente para crear la base de datos:

El asistente posee varias secciones, en la primera podemos:

• Verificar el nombre del compartimiento.
• Ingresar el nombre "Visible" y el nombre "real" de nuestra nueva base de datos.
• Seleccionar el tipo de base de datos autónoma a crear (como seleccionamos la opción habiendo entrado inicialmente desde la parte "Autonomous JSON Database, la misma aparece pre-seleccionada).

Luego podremos elegir:

• Si usar infraestructura dedicada (un servidor exadata "privado") o compartida (nuestra DB estará ubicada en un servidor exadata con otras DB de otros clientes de Oracle.
• La versión de la base de datos Oracle (por el momento, solo ofrece Oracle 19c).
• Cantidad de procesadores y espacio de almacenamiento.
• La opción de Auto Escalar para aumentar la capacidad de procesamiento y almacenamiento en forma automática.

A continuación debemos ingresar la contraseña para el usuario Admin de nuestra base de datos:

 

Y por último, podemos elegir:

• Permitir el acceso público a nuestra DB (opción por defecto) o configurar la misma para que se encuentre ubicada en una VCN de nuestra cuenta, teniendo que configurar los permisos necesarios.
• Usar una licencia de Oracle o usar una licencia ya existente (esta opción no se encuentra disponible para JSON Autonomous Database).

Por último, seleccionamos el botón "Create Autonomous Database" (como se muestra en la imagen anterior) y el proceso de creación de la instancia se inicia en forma automática, como vemos a continuación:

Conectándonos a una Base de Datos JSON Autónoma desde SQL Developer

Una vez finalizado el proceso de provisionamiento de nuestra base de datos JSON autónoma, podemos conectarnos a ella desde cualquier computadora con acceso a internet. Este proceso es similar al de cualquier otra instancia de Oracle Autonomous Database (ATP o ADW).

En primer lugar, desde la consola de Oracle Cloud, vamos a seleccionar la opción "DB Connection":

En esta pantalla podremos elegir el tipo de Wallet que queremos descargar, la opción por defecto es descargar el Wallet de la Instancia, lo cual hacemos presionando el botón "Download Wallet":

A continuación deberemos ingresar una contraseña para el Wallet (algunas herramientas lo requieren para acceder al mismo):

Una vez que descargamos el Wallet, solo tenemos que abrir el SQL Developer y configurar la conexión, con los siguientes detalles

• En "Usuario" y "Contraseña", utilizar el usuario "Admin", y la contraseña definida para el mismo en la tercera parte del asistente de creación de la DB.
• En la opción "Tipo de Conexión", seleccionar "Cartera de Cloud" (Cloud Wallet).
• En la opción "Archivo de Configuración", seleccionar el archivo de Wallet que acabamos de descargar.
• En la opción "Servicio" seleccionar uno de los servicios ya definidos, normalmente utilizaremos el servicio que finaliza en  TP (Transaction Processing)

Una vez que probamos la conexión, podemos guardarla y conectarnos a la misma, ejecutando una query para comprobar que la conexión funciona correctamente:

Oracle Presentó "Oracle Autonomous JSON Database"

Hace pocas horas Oracle anunció la disponibilidad de un nuevo servicio de Base Datos autónomo llamado "Oracle Autonomous JSON Database" como parte de Oracle Cloud Infrastructure. El anuncio, en el blog de JSON de Oracle, fue realizado por Beda Hammerschmidt.

Oracle Autonomous JSON Database es un servicio orientado a desarrolladores que desean acceso a una base de datos JSON potente y económica mediante una API NoSQL. El servicio posee todas las características de MongoDB, en una plataforma Cloud altamente probada, con la elasticidad de OCI, soporte al estándar Full ACID y funcionalidad SQL.

El servicio "Autonomous JSON Database" proporciona una gran cantidad de funciones que no se encuentran en bases de datos NoSQL menos maduras:

• Algoritmos de aprendizaje automático integrados (Machine Learning).
• Consultas y tipos de datos Spatial.
• Características de seguridad avanzadas como control de acceso detallado.
• Lenguaje procedural (PL/SQL) del lado del servidor.
• Entorno de desarrollo Low Code (Oracle APEX).
• Transacciones ACID sin límites de tiempo o tamaño de transacción.
• Joins y / o agregaciones de recopilación cruzada simples y rápidas.
• Índices de búsqueda inteligentes en documentos JSON completos.

Como el 25/08 estaré presentando en Groundbreakers Tour LATAM 2020 sobre Oracle 20c y las mejoras en JSON (pueden ver los detalles aquí) seguramente en los próximos días voy a estar investigando esta nueva opción de Oracle Cloud y publicando un tutorial sobre la misma

Voy a estar presentando en Groundbreaker Tour 2020 Latam Virtual

En la semana recibí la aceptación por parte de LAOUC (Latin America Oracle User Community) de mi charla "¿Que hay de nuevo en JSON en Oracle 20c?" para el Groundbreakers Tour 2020 LATAM.

La misma será el martes 25 de agosto (aunque la agenda puede estar sujeta a modificaciones), y como ya comenté previamente, será en forma virtual, debido a las restricciones de viajes y reuniones causadas por el COVID-19.

Para ver mas detalles sobre el evento, pueden consultar la página de LAOUC del mismo aquí. Apenas tenga los detalles sobre la inscripción, incluiré los mismos en el blog.

Nota de Ultimo Momento!

Ya se encuentra abierta la inscripción a las charlas!!!

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;

Oracle 19c - Mejoras en el uso de JSON_TABLE en Vistas Materializadas

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 - Mejoras a JSON_OBJECT

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;