Almacenamiento de datos formateados en JSON en SQL Server 2016

En este artículo, continúo revisando las emocionantes características en SQL Server 2016. Una de tales características es el largamente esperado soporte de T-SQL para datos formateados en JSON. En este artículo damos un vistazo a cómo el soporte de JSON impactará las soluciones de almacén de datos.

Antecedentes

Desde el advenimiento del Lenguaje de Márgenes Extensible (XML, por sus siglas en inglés), muchas aplicaciones web modernas se han enfocado en proveer datos que son al mismo tiempo legibles para humanos y máquinas. Desde una perspectiva de base de datos relacional, SQL Server se mantuvo con estas aplicaciones web modernas proveyendo soporte para datos XML en una forma de tipo de datos XML y muchas funciones que podrían ser usadas para analizar gramáticamente, consultar y manipular datos con formato XML.

Como resultado de ser soportado en SQL Server, las soluciones de almacén de datos basadas en SQL Server pudieron entonces sacar datos basados en XML OLTP en un emporio de datos. Para ilustrar este punto, demos un vistazo a la presentación XML de nuestros datos ficticios de Ventas de Frutas mostrada en la Figura 1.

Figura 1: Representación XML de los datos de Ventas de Frutas (Fruit Sales)

Para procesar estos datos en el almacén de datos, tendríamos primero que convertirlos en un formato relacional de filas y columnas usando las funciones T-SQL integradas de XML, como la función nodes(). Figura 2 muestra un script que hace uso de la función nodes() para convertir los datos de muestra mostrados en la Figura 1.

Figura 2: Representación XML de los datos de Ventas de Frutas (Fruit Sales)

Lo resultados del script de arriba son mostrados en la Figura 3 en un formato reconocible para el almacén de datos:

Figura 3: Los datos XML representados en un formato de tabla

Muy poco después de que XML se volviera un lenguaje dominante para intercambio de datos para cualquier aplicación web moderna, Notación de Objeto JavaScript (JSON) fue presentada como un formato ligero de intercambio de datos que es más conveniente de procesar para aplicaciones web que XML. Igualmente, la mayoría de los productores de bases de datos relacionales lanzaron versiones nuevas de sus sistemas de bases de datos que incluían soporte para datos con formato JSON. Desafortunadamente, Microsoft SQL Server no fue uno de ellos, y hasta SQL Server 2014, JSON no era soportado. Obviamente esta falta de soporte para JSON creó desafíos para los ambientes de almacén de datos que estaban basados en SQL Server.

Aunque hay soluciones alternativas (por ejemplo, usando (i.e. using Json.Net) ) para tratar la falta de soporte para JSON en SQL Server, siempre había la sensación de que estas soluciones alternativas no eran adecuadas, desperdiciaban tiempo y estaban forzando a los equipos de desarrollo de almacén de datos a elegir una nueva habilidad (por ejemplo, aprender .NET). Afortunadamente, el lanzamiento de SQL Server 2016 ha asegurado que los equipos de desarrollo puedan dejar de lado sus soluciones alternativas para JSON, ya que éste es soportado en SQL Server 2016.

Analizando gramáticamente Datos JSON para el Almacén de Datos

De manera similar al soporte de XML en SQL Server, el soporte para JSON puede ser clasificado en dos maneras:

Convertir un conjunto de datos Relacional a formato JSON

Convertir un conjunto de datos JSON a formato relacional

De todas maneras, para los propósitos de esta discusión, nos enfocaremos primariamente en la segunda parte, la cual es convertir datos con formato JSON (recuperados desde fuentes OLTP) a un formato relacional de filas y columnas. Para ilustrar nuestros puntos de discusión, una vez más hacemos uso del conjunto de datos ficticio de ventas de frutas. Esta vez, el conjunto de datos ficticio ha sido convertido a formato JSON, como se muestra en la Figura 4.

Figura 4: Representación JSON del conjunto de datos de Ventas de Frutas (Fruit Sales)

Función ISJSON

Como parte de soportar datos formateados en JSON en otras bases de datos relacionales como MySQL y PostgreSQL 9.2 hay un tipo de datos JSON separado que ha sido presentado por estos productores. Entre otras cosas, el tipo de datos JSON conduce revisiones de validación para asegurar que los valores están siendo almacenados realmente en formato JSON válido.

Desafortunadamente, SQL Server 2016 (y ORACLE 12c) no tienen un tipo especial de dato para guardar datos JSON y, en su lugar, un tipo de datos de carácter variable (varchar/nvarchar) es usado. Por lo tanto, un procedimiento recomendado para lidiar con datos JSON en SQL Server 2016 es, primeramente, asegurar que usted está realmente lidiando con datos JSON válidos. La manera más simple de hacer esto es usar la función ISJSON. Esta es una función T-SQL integrada que retorna 1 para un conjunto de datos JSON válido y 0 para uno no válido.

La Figura 5 nos muestra la implementación de la función ISJSON, por la cual validamos nuestro conjunto de datos ficticio de muestra.

Figura 5: Validando nuestros datos JSON ficticios usando ISJSON

Función OPENJSON

Ahora que hemos confirmado que estamos trabajando con un conjunto de datos JSON válido, el siguiente paso es convertir los datos a un formato de tabla. De nuevo, tenemos una función T-SQL integrada para hacer esto. OPENJSON funciona de manera similar a OPENXML en que toma un objeto y convierte sus datos en filas y columnas.

LaFigura 6 muestra un script T-SQL completo para convertir un objeto JSON a filas y columnas.

Figura 6: Script de muestra T-SQL OPENJSON

Una vez que ejecutamos el script de arriba, obtenemos la salida relacional mostrada en la Figura 7.

Figura 7: Salida T-SQL de OPENJSON

Ahora que tenemos nuestro conjunto de datos relacional, podemos procesar estos datos al almacén de datos.

Función JSON_VALUE

Previamente a concluir nuestra discusión acerca de JSON en SQL Server 2016, vale la pena mencionar que en adición a OPENJSON, usted tiene otras funciones como JSON_VALUE, que podría ser usada para consultar datos JSON. De todas maneras, esta función retorna un valor escalar, lo que significa que, a diferencia de las múltiples filas y columnas retornadas usando OPENJSON, JSON_VALUE retorna un solo valor, como se muestra en la Figura 8.

Figura 8: Salida de un solo valor usando la función JSON_VALUE

Si el objeto JSON que usted está consultando no tiene múltiples elementos, entonces usted no tiene que especificar el índice de la fila (por ejemplo, [0]), como se muestra en la Figura 9.

Figura 9: Script T-SQL JSON_VALUE sin índice de fila

Conclusión

La larga espera ha terminado finalmente y con el lanzamiento de SQL Server 2016, JSON ahora es soportado. De forma similar a XML, T-SQL soporta la conversión de objetos JSON a formato relacional, así como la conversión de tablas relacionales a un objeto JSON. Este soporte es implementado vía funciones T-SQL integradas, como OPENJSON y JSON_VALUE. A pesar de toda la emoción con el soporte de JSON en SQL Server 2016, aún no tenemos un tipo de datos JSON. La función ISJSON puede entonces ser usada para validar texto JSON.

Descargas

Sifiso Ndlovu

Sifiso es un profesional basado en Johannesburgo dentro de un amplio rango de Competencias en Tecnología Microsoft como SQL Server y Visual Studio Application Lifecycle Management.
Él es un miembro del Grupo de Usuarios de SQL de Johannesburgo y tiene un grado de maestría en MCom IT Management de la Universidad de Johannesburgo.
Actualmente, él trabaja para Sambe Consulting como Consultor principal.

Ver todas las publicaciones de Sifiso W. Ndlovu

Últimas entradas de Sifiso Ndlovu (ver todo)

Entendiendo el Impacto de las sugerencias NOLOCK y WITH NOLOCK en SQL Server - May 28, 2018
Cómo reemplazar caracteres especiales ASCII en SQL Server - May 25, 2018
Convertir resultados de SQL Server a JSON - June 2, 2017