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 NULLCONSTRAINT 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;
DESC vdComprobanteName 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.FechaComprobanteFROM 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:00SELECT 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.
No hay comentarios.:
Publicar un comentario