Oracle 19c - Nueva Función JSON_MERGEPATCH

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: