Uso de una interfaz SQL para manejar datos JSON en Db2 for z/OS
Este tutorial se centra en una interfaz SQL en Db2 for z/OS® que le permite extraer y recuperar datos JSON de objetos BSON y convertir de JSON a BSON. Al utilizar esta interfaz, puede gestionar datos JSON sin depender de las API JSON de Db2 NoSQL. En su lugar, se pueden utilizar las interfaces SQL para la manipulación del JSON. Se proporciona orientación sobre la instalación y la configuración, así como muchos ejemplos para el uso común de JSON dentro de Db2 for z/OS. Se proporcionan sugerencias y recomendaciones para mejorar el rendimiento e impedir posibles problemas.
Introducción
Db2 for z/OS admite el almacenamiento y la manipulación de documentos JSON. Para la manipulación de la documentación, se basa en las API JSON NoSQL Db2 . Los datos JSON se almacenan internamente como BSON en una columna BLOB en línea. La conversión entre JSON y BSON se gestiona mediante las API JSON de Db2 NoSQL.
>>-JSON_VAL—(--json-value--,--search-string--,--result-type--)---------><La función JSON_VAL devuelve un elemento de un documento JSON que se identifica por el nombre de campo JSON especificado en search-string. El valor del elemento JSON se devuelve en el tipo de datos y la longitud que se especifican en result-type. Para más información, consulte la función escalar JSON_VAL.
Estas dos funciones de desarrollo de usuario, junto con JSON_VAL, le permiten utilizar SQL para realizar manipulaciones básicas de datos JSON. La invocación de estas funciones e Db2 es se denomina interfaces SQL (o API) en este tutorial.
¿Qué es JSON?
JavaScript Object Notation (JSON) es un formato de datos ligero especificado en IETF RFC 4627 y se basa en un subconjunto del lenguaje de programación JavaScript. JSON es un formato de texto que es fácil de leer y escribir para los humanos, y fácil de analizar y generar para las máquinas. Con la creciente popularidad de JavaScript y la simplicidad del propio JSON, este formato se ha hecho popular para presentar información a clientes de Internet ( JavaScript ).
La siguiente figura muestra un ejemplo sencillo de un documento JSON que ilustra una orden de compra.
{
"PO": {
"@id": 123,
"@orderDate": "2013-11-18",
"customer": { "@cid": 999 },
"items": {
"item": [
{
"@partNum": "872-AA",
"productName": "Lawnmower",
"quantity": 1,
"USPrice": 149.99,
"shipDate": "2013-11-20"
},
{
"@partNum": "945-ZG",
"productName": "Sapphire Bracelet",
"quantity": 2,
"USPrice": 178.99,
"comment": "Not shipped"
}
]
}
}
}Instalación y configuración
Para habilitar la compatibilidad con JSON en Db2 12 for z/OS , necesita los UDF del lado del servidor para el acceso a documentos JSON, que están disponibles en Db2 Accessories Suite for z/OS , versión 4.1 (FMID H2AS410 ).
Para crear los UDF del lado del servidor, ejecute el DDL en el conjunto de accesorios de Oracle ( Db2 ). Debe configurarse un entorno WLM para estos UDF.
La mayoría de estas UDF son ayudantes que normalmente no utilizamos directamente en una instrucción SQL, excepto SYSTOOLS.JSON2BSON y SYSTOOLS.BSON2JSON. Las dos siguientes sentencias CREATE FUNCTION ilustran la definición de estas UDF.
CREATE FUNCTION SYSTOOLS.JSON2BSON
( INJSON CLOB(16M)
)
RETURNS BLOB(16M)
SPECIFIC JSON2BSON
LANGUAGE C
PARAMETER STYLE SQL
PARAMETER CCSID UNICODE
NO SQL
WLM ENVIRONMENT DSNWLM_GENERAL
RUN OPTIONS 'XPLINK(ON)'
PROGRAM TYPE SUB
DETERMINISTIC
DISALLOW PARALLEL
DBINFO
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
EXTERNAL NAME 'DSN5JSJB';CREATE FUNCTION SYSTOOLS.BSON2JSON
( INBSON BLOB(16M)
)
RETURNS CLOB(16M)
SPECIFIC BSON2JSON
LANGUAGE C
PARAMETER STYLE SQL
PARAMETER CCSID UNICODE
WLM ENVIRONMENT DSNWLM_GENERAL
RUN OPTIONS 'XPLINK(ON)'
DBINFO
PROGRAM TYPE SUB
DISALLOW PARALLEL
NO SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
NO EXTERNAL ACTION
EXTERNAL NAME 'DSN5JSBJ';Tenga en cuenta que JSON2BSON( ) y BSON2JSON( ) no pueden aplicarse en la columna VARCHAR.
Las siguientes secciones muestran cómo crear una tabla para almacenar, insertar, seleccionar y actualizar datos JSON.
Creación de una tabla para almacenar documentos JSON
Para utilizar las API de SQL para manipular documentos JSON, el tipo de datos de la columna que se utiliza para almacenar documentos JSON debe declararse como un BLOB en línea. Las dos primeras sentencias SQL de la Figura 4 muestran cómo crear una tabla para almacenar datos JSON. Aunque estas tablas utilizan la misma definición que la tabla creada implícitamente por la API JSON, no es necesaria si solo utiliza API SQL. Al adoptar este enfoque, tendrá más flexibilidad para controlar los objetos de la base de datos para almacenar y manipular datos JSON que los objetos creados implícitamente por la API JSON. Por ejemplo, puede crear la tabla en un espacio de tabla o base de datos en particular, y utilizar un búfer de memoria intermedia en particular; puede definir una longitud en línea distinta de 25000, una longitud de columna BLOB distinta de 16M, etc.
CREATE TABLE JSONPO( ID INTEGER NOT NULL,
DATA BLOB(16M) INLINE LENGTH 25000,
PRIMARY KEY(ID)) CCSID UNICODE;
CREATE TABLE JSONCUSTOMER
( ID INTEGER NOT NULL,
DATA BLOB(16M) INLINE LENGTH 25000,
PRIMARY KEY(ID)) CCSID UNICODE;
CREATE TABLE JSONPOTXT( DATA VARCHAR(5000)) CCSID UNICODE;La tabla JSONPO almacenará la orden de compra en formato binario de JSON (BSON) en una columna BLOB en línea. La tabla JSONCUSTOMER almacenará la información del cliente en formato binario de JSON(BSON) en una columna BLOB en línea. La tabla JSONPOTEXT almacenará el formato JSON del pedido de compra en una columna VARCHAR.
Inserción de documentos JSON
Db2 almacena documentos JSON en formato BSON, por lo que necesitamos una función para convertir texto JSON a formato BSON para insertar documentos JSON en tablas d Db2 . SYSTOOLS.JSON2BSON sirve para este propósito: recibe datos JSON en formato de texto y devuelve el formato BSON al llamante. La siguiente instrucción INSERT de ejemplo muestra cómo insertar datos JSON en la tabla invocando la función SYSTOOLS.JSON2BSON.
INSERT INTO JSONPO VALUES (
101,
SYSTOOLS.JSON2BSON(
'{"PO":{"@id": 101,
"@orderDate": "2014-11-18",
"customer": {"@cid": 999},
"items": {
"item": [{"@partNum": "872-AA",
"productName": "Lawnmower",
"quantity": 1,
"USPrice": 149.99,
"shipDate": "2014-11-20"
},
{"@partNum": "945-ZG",
"productName": "Sapphire Bracelet",
"quantity": 2,
"USPrice": 178.99,
"comment": "Not shipped"
}
]
}
}
}')
);
INSERT INTO JSONPO VALUES (
102,
SYSTOOLS.JSON2BSON(
'{"PO":{"@id": 102,
"@orderDate": "2014-12-20",
"customer": {"@cid": 888},
"items": {
"item": [{"@partNum": "872-AA",
"productName": "Lawnmower",
"quantity": 1,
"USPrice": 749.99,
"shipDate": "2014-12-21"
},
{"@partNum": "837-CM",
"productName": "Digital Camera",
"quantity": 2,
"USPrice": 199.99,
"comment": "2014-12-22"
}
]
}
}
}'));
INSERT INTO JSONCUSTOMER VALUES (
101,
SYSTOOLS.JSON2BSON(
'{"Customer":{"@cid": 999,
"name": "Michael",
"age": 31,
"telephone": "234-343-2343",
"country": "USA"
}
}'));
INSERT INTO JSONCUSTOMER VALUES (
102,
SYSTOOLS.JSON2BSON(
'{"Customer":{"@cid": 888,
"name": "George",
"age": 29,
"telephone": "133-144-9999",
"country": "USA"
}
}'));INSERT INTO JSONPOTXT VALUES (
'{"PO":{"@id": 103,
"@orderDate": "2014-06-20",
"customer": {"@cid": 888},
"items": {
"item": [ { "@partNum": "872-AA",
"productName": "Lawnmower",
"quantity": 1,
"USPrice": 749.99,
"shipDate": "2014-06-21"
},
{ "@partNum": "837-CM",
"productName": "Digital Camera",
"quantity": 2,
"USPrice": 199.99,
"comment": "2014-06-22"
}
]
}
}
}');Selección de documentos JSON completos
Si selecciona el documento JSON directamente de la columna sin utilizar ninguna función, verá un documento JSON en formato BSON no muy legible (porque Db2 almacena JSON como BSON internamente, como se muestra en la siguiente figura).
SELECT DATA FROM JSONPO WHERE ID=101;
DATA
34a01000003504f004101000010406964006500000002406f7264657244617465000b00000032303134
2d31312d31380003637573746f6d6572000f000000104063696400e703000000036974656d7300f8000
000046974656d00ed0000000330006d0000000240706172744e756d00070000003837322d4141000270
726f647563744e616d65000a0000004c61776e6d6f77657200107175616e74697479000100000001555
350726963650048e17a14aebf6240027368697044617465000b000000323031342d31312d3230000003
3100750000000240706172744e756d00070000003934352d5a47000270726f647563744e616d6500120
00
1 record(s) selectedPara ver el documento JSON en un formato legible, debe invocar la función SYSTOOLS.BSON2JSON. Esta función recibe los datos JSON en formato BSON y devuelve el formato de texto al emisor de la llamada. Por lo tanto, al pasar la columna con el documento JSON a la función SYSTOOLS.BSON2JSON, obtendrá datos JSON legibles. La siguiente instrucción SELECT de ejemplo ilustra cómo utilizar BSON2JSON( ) para recuperar el documento JSON en formato de texto.
SELECT SYSTOOLS.BSON2JSON(DATA)
FROM JSONPO;Creación de índice JSON
Para mejorar el rendimiento durante SELECT, puede crear un índice en PO.customer.@cid en JSON, como se muestra en la siguiente figura. Primero, escriba una expresión utilizando JSON_VAL para recuperar el PO.customer.@cid. Su tipo de devolución deseado es un número entero, por lo que se especifica i . Además, debe añadir :na para asegurarse de que no se devuelve el tipo de matriz porque Db2 aún no admite un tipo de matriz. También se puede crear un índice compuesto. Puede escribir varias expresiones JSON_VAL en un único índice para mejorar el rendimiento en varios campos de un documento JSON.
CREATE INDEX IX1 ON JSONPO(
JSON_VAL(DATA, 'PO.customer.@cid','i:na'));Selección de parte de un documento JSON
Para recuperar el valor de un campo específico en un documento JSON, debe invocar una función JSON_VAL, que es una función incorporada que proporciona una interfaz SQL para extraer y recuperar datos JSON en tipos de datos SQL a partir de objetos BSON. Su esquema es SYSIBM. Esta función solo acepta el tipo BSON de documento JSON, por lo que su argumento debe ser una columna de la tabla que contiene el documento JSON en formato BSON o una funció SYSTOOLS.JSON2BON que devuelva el formato BSON del documento JSON.
La siguiente instrucción SELECT de ejemplo muestra cómo invocar JSON_VAL para recuperar el valor del campo JSON en el tipo de datos SQL. Buscamos el primer e productName a en PO.items.item para PO.customers.@cid=999.
SELECT JSON_VAL(DATA, 'PO.items.item.0.productName', 's:10')
FROM JSONPO
WHERE JSON_VAL(DATA,'PO.customer.@cid', 'i:na') = 999;Salida esperada:
Lawnmower- Si especifica una ruta que no existe, JSON_VAL devuelve un valor nulo en lugar de un mensaje de error, como se muestra en la Figura 11.
- JSON_VAL también devolverá un valor nulo si los datos JSON no pueden convertirse al tipo de datos especificado debido a una incompatibilidad de tipos. En la Figura 12, queremos convertir PO.items.item.0.productName en un entero, y se devolverá un valor nulo.
- Si se especifica
:naen el tercer argumento de la invocación de la función y se encuentra una matriz, se devuelve un error. - Si no se especifica explícitamente
:na(por defecto), JSON_VAL devolverá el primer elemento de la matriz, cuando se encuentre una matriz.
SELECT JSON_VAL(DATA, 'PO.productName', 's:10') FROM JSONPO;Salida esperada:
<null>
<null>
2 record(s) selectedSELECT JSON_VAL(DATA, 'PO.items.item.0.productName', 'i')
FROM JSONPO;Salida esperada:
<null>
<null>
2 record(s) selectedLa siguiente tabla muestra los tipos de resultados compatibles de JSON_VAL:
| Tipo de resultado | Función tipo/longitud de retorno | Notas |
|---|---|---|
| 'n' | DECFLOAT(34) | |
| «i» | ENTERO | |
| 'L' | BIGINT | |
| 'F' | DOUBLE | |
| «d» | FECHA | |
| «ts» | TIMESTAMP | |
| «t» | HORA | |
| 's:n' | VARCHAR(n) | 'n' significa bytes de retorno de los datos resultantes. Devuelve el valor nulo si los datos del resultado son más largos que 'n'. «n» es un valor entero en el rango de 1 a 32672. |
| «b:n» | VARCHAR(n) FOR BIT DATA | 'n' significa bytes de retorno de los datos resultantes. Devuelve el valor nulo si los datos del resultado son más largos que 'n'. «n» es un valor entero en el rango de 1 a 32672 |
| «Usted» | ENTERO / 4 | Devuelve 0 para los elementos cuyos valores se establecen explícitamente como NULL en el documento JSON. Si el valor está establecido, pero NO está establecido en NULL, devuelve 1. Para los campos que falten, se devolverá el valor nulo. |
Selección de datos de tablas JSON unidas
Podemos consultar datos JSON de varias tablas en una sola instrucción SQL. En la siguiente sentencia SELECT de ejemplo, buscamos todos los nombres de clientes ( Customer.name ) de la tabla JSONCUSTOMER en la que PO.customer.@cid en la tabla JSONPO es igual a Customer.@cid en JSONCUSTOMER.
SELECT JSON_VAL(T2.DATA, 'Customer.name', 's:20') as "Customer Name"
FROM JSONPO T1, JSONCUSTOMER T2
WHERE JSON_VAL(T1.DATA, 'PO.customer.@cid', 'i') = JSON_VAL(T2.DATA, 'Customer.@cid', 'i');Salida esperada:
Customer Name
Michael
George
2 record(s) selectedCombinar varios documentos JSON
Puede combinar dos o más documentos JSON en una sola consulta utilizando el operador de conjunto. La siguiente instrucción SQL de ejemplo combina dos documentos JSON de diferentes fuentes mediante un operador de conjunto UNION. Este ejemplo tiene un operador UNION con dos ramas: una rama contiene datos JSON de la tabla JSONPO y la otra rama contiene datos JSON de la tabla JSONPOTXT. Debido a que la tabla JSONPOTXT almacena JSON en la columna VARCHAR, debe utilizar JSON2BSON para convertir el formato textual a formato binario, y luego utilizar BSON2JSON para convertirlo a formato JSON antes de UNION.
SELECT SYSTOOLS.BSON2JSON(DATA) FROM JSONPO
UNION ALL
SELECT SYSTOOLS.BSON2JSON(
SYSTOOLS.JSON2BSON((SELECT DATA FROM JSONPOTXT))
) AS DATA1
FROM SYSIBM.SYSDUMMY1;Observe que aquí se utiliza JSON2BSON( ) para verificar que los DATOS de la tabla JSONPOTXT están en un formato JSON válido.
Clasificación de documentos JSON
Puede ordenar los documentos JSON invocando la función JSON_VAL en la cláusula ORDER BY. La siguiente sentencia SELECT de ejemplo devuelve documentos JSON ordenados (de forma descendente) por el campo PO.customer.@cid.
SELECT SYSTOOLS.BSON2JSON(DATA)
FROM JSONPO
ORDER BY JSON_VAL(DATA, 'PO.customer.@cid', 'i') DESC;Actualización de documentos JSON
Puede actualizar un documento JSON en la tabla. La siguiente instrucción SQL de ejemplo actualiza un documento JSON invocando dos funciones: " SYSTOOLS.JSON2BSON " y "JSON_VAL". En este ejemplo, se invoca JSON_VAL en la condición de búsqueda para encontrar el registro que se va a actualizar ('Customer.@cid'
=888), y se invoca SYSTOOLS.JSON2BSON para pasar los nuevos datos JSON.
UPDATE JSONCUSTOMER
SET DATA = SYSTOOLS.JSON2BSON(
'{"Customer":{"@cid": 777,
"name": "George",
"age": 29,
"telephone": "566-898-1111",
"country": "USA"
}}')
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na') = 888;Eliminación de documentos JSON
De forma similar a la operación ACTUALIZAR en documentos JSON, puede eliminar un documento JSON de la tabla. La siguiente instrucción SQL de ejemplo elimina un documento JSON. Al invocar JSON_VAL en la condición de búsqueda de la instrucción DELETE, puede localizar el registro que se va a eliminar ('Customer.@cid'
=777), y la instrucción DELETE elimina los datos JSON si los encuentra.
DELETE JSONCUSTOMER
WHERE JSON_VAL(DATA, 'Customer.@cid', 'i:na') = 777#