2017-04-24

Abril de 2017

Volumen 32, número 4

Vanguardia: consultar datos JSON en SQL Server 2016

Por Dino Esposito | Abril de 2017

Dino Esposito Mover datos por sistemas independientes y autónomos es lo que la mayoría de las aplicaciones de software hace actualmente, y JSON es el lenguaje ubicuo que se encuentra detrás de la transferencia de datos. JSON, abreviatura de JavaScript Object Notation, es un método basado en texto para disponer el estado de un objeto, de manera que se pueda serializar y transferir fácilmente por cable de un sistema al siguiente, especialmente en sistemas heterogéneos.

JSON se convirtió en lo que, en última instancia, XML no pudo ser: la lengua franca de Internet. Personalmente, no creo demasiado en el hecho de que JSON sea más fácil de leer que XML. Por otro lado, JSON es un formato de texto mucho más compacto y ligero que XML, que pueden editar los humanos, y que los equipos pueden analizar y comprender rápidamente a través de una larga lista de plataformas de software y hardware.

Una cadena JSON es una cadena de texto sin formato. Cualquier versión del sistema de administración de bases de datos relacionales (RDBMS), incluido SQL Server, permite almacenar una cadena independientemente del diseño del contenido. No obstante, SQL Server 2016, es la primera versión de base de datos de Microsoft que permite leer y guardar datos tabulares existentes como JSON, y, lo más importante, realizar consultas en cadenas JSON como si el contenido JSON fuese realmente una colección de columnas individuales.

Para obtener información general estructurada y completa de las funciones de JSON en SQL Server 2016, lea la documentación de MSDN en bit.ly/2llab1n. Además, encontrará un resumen ejecutivo excelente de JSON en SQL Server 2016 en el artículo de Simple Talk en bit.ly/26rprwv. El artículo ofrece una vista de JSON más orientada a la empresa en SQL Server 2016 y, en general, una perspectiva basada en el escenario del uso de datos JSON en un nivel de persistencia relacional.

Datos JSON en el nivel de persistencia

Existen dos verbos clave para comprender la finalidad de JSON: transmitir y serializar. Por lo tanto, JSON es el formato en que se dispone el estado de una entidad de software, a fin de poder transmitirse entre espacios del proceso con la certeza de que se comprenderá bien en ambos extremos. Perfecto, pero esta es una columna sobre JSON en SQL Server y, por consiguiente, en el nivel de persistencia. Empecemos con la pregunta fundamental: ¿Cuándo guardaría datos en SQL Server con el formato JSON?

Una tabla de base de datos relacional se articula en un número fijo de columnas y cada columna tiene su propio tipo de datos, como cadenas de longitud fija o variable, fechas, números, valores booleanos y otros similares. JSON no es un tipo de datos nativo. Una columna de SQL Server que contiene datos JSON es una columna de cadena sin formato desde la perspectiva de bases de datos. Los datos JSON se escriben en una columna de tabla del mismo modo que escribiría una cadena regular y puede hacerlo en cualquier versión de SQL Server, así como en cualquier otro servicio RDBMS.

¿Dónde se obtienen las cadenas JSON que se almacenan finalmente en una base de datos? Existen dos escenarios principales: El primero, esas cadenas podrían proceder de un servicio web o de alguna otra forma de punto de conexión externo que transmita datos (por ejemplo, un sensor o un dispositivo conectado). El segundo, los datos JSON podrían ser un práctico método para agrupar fragmentos de información relacionados, de modo que apareciesen como un único elemento de datos. Esto suele suceder al tratar con datos semiestructurados, tales como datos que representan un evento de negocio para almacenar en un escenario de abastecimiento de eventos o, más simplemente, en un contexto empresarial inherentemente controlado por eventos, como los sistemas de dominios en tiempo real (por ejemplo, de finanzas, comercio, puntuación, supervisión, control y automatización industrial, etc.). En todos estos casos, su almacenamiento se puede normalizar en un formulario estructurado que serialice la información relacionada de longitud y formato variables en un solo elemento de datos que cabría en la columna de cadena de una tabla relacional.

Como mencioné, el contenido JSON que podría persistir puede proceder de un origen externo o se puede generar por medio de la serialización desde instancias de objetos de C#:

foreach (var c in countries)
{
  // Serialize the C# object to JSON
  var json = JsonConvert.SerializeObject(c);
  // Save content to the database
  record.JsonColumn = json;
}

También puede usar Entity Framework (EF) para guardar datos JSON en una columna de una tabla de base de datos.

SQL Server 2016 lleva este nivel más lejos y permite transformar datos JSON en filas de tabla. Esta capacidad puede ahorrarle una gran cantidad de trabajo y de ciclos de la CPU del código, ya que ahora puede insertar el texto JSON sin formato en la base de datos sin tener que analizarlo primero en objetos de C# en el código de la aplicación y luego pasarlo a través de EF o de llamadas ADO.NET directas. La clave para alcanzar este objetivo es la nueva función OPENJSON:

declare @country nvarchar(max) = '{
  "id" : 101,
  "name": "United States",
  "continent": "North America"
}';
  INSERT INTO Countries
    SELECT * FROM OPENJSON(@country)
    WITH (id int,
      name nvarchar(100),
      continent nvarchar(100))

Puede usar la función para insertar o actualizar filas de tabla regulares a partir de texto JSON sin formato. La cláusula WITH permite asignar propiedades JSON a columnas existentes de la tabla.

Escenario de Event Sourcing

En mi columna de diciembre de 2016, describí Event Sourcing como un patrón emergente para almacenar el estado histórico de la aplicación (msdn.com/magazine/mt790196). En lugar de guardar el último estado correcto conocido, con Event Sourcing se guarda cada uno de los eventos de negocio que altera el estado y se recompila el estado más reciente mediante la reproducción de los eventos anteriores.

El aspecto crucial de una implementación de Event Sourcing es la eficacia a la hora de guardar y recuperar los eventos anteriores. Cada evento es diferente y puede tener un esquema distinto, según el tipo y la información disponible. Al mismo tiempo, tener un almacén distinto (relacional) para cada tipo de evento es un problema, porque los eventos se producen de forma asincrónica y pueden afectar a distintas entidades y distintos segmentos del estado. Si los mantiene en tablas diferentes, recompilar el estado puede ser una operación costosa debido a las cláusulas JOIN entre tablas. Por consiguiente, guardar eventos como objetos es la opción más recomendada y los almacenes NoSQL hacen su trabajo muy bien. ¿Es posible usar Event Sourcing con una base de datos relacional en su lugar?

Guardar el evento como JSON es una opción posible en cualquier versión de SQL Server, pero la lectura eficaz de JSON con cantidades elevadas de eventos en el almacén podría ser insostenible. Con las características nativas de JSON en SQL Server 2016, al panorama cambia y el uso de SQL Server en un escenario de Event Sourcing pasa a ser realista. No obstante, ¿cómo realizaría una consulta de JSON desde una tabla de base de datos?

Consulta de datos a partir del contenido de JSON

Supongamos que se las apaña para tener una o más columnas de datos JSON en una tabla relacional canónica. Por tanto, las columnas con datos primitivos y las columnas rellenadas con datos JSON se encuentran en paralelo. Salvo que se usen las nuevas funciones de SQL Server 2016, las columnas JSON se tratan como campos de texto sin formato y se pueden consultar solo con la cadena T-SQL e instrucciones de texto, como LIKE, SUBSTRING y TRIM. Para el propósito de la demo, compilé una columna denominada Countries (con algunas columnas tabulares) y otra denominada Serialized, que contiene el resto del registro serializado como JSON, como se muestra en la Figura 1.

Figura 1 Base de datos de países de muestra con una columna JSON

El objeto JSON serializado en la tabla de muestra tiene la apariencia siguiente:

{
  "CountryCode":"AD",
  "CountryName":"Andorra",
  "CurrencyCode":"EUR",
  "Population":"84000",
  "Capital":"Andorra la Vella",
  "ContinentName":"Europe",
  "Continent":"EU",
  "AreaInSqKm":"468.0",
  "Languages":"ca",
  "GeonameId":"3041565",
  "Cargo":null

La consulta T-SQL siguiente muestra cómo seleccionar solo los países que cuentan con más de 100 millones de habitantes. La consulta combina columnas de tabla regulares y propiedades JSON:

SELECT CountryCode,
  CountryName,
  JSON_VALUE(Serialized, '$.Population') AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized, '$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

La función JSON_VALUE toma el nombre de una columna JSON (o una variable local establecida en una cadena JSON) y extrae el valor escalar siguiendo la ruta de acceso especificada. Como se muestra en la Figura 2, el símbolo $ hace referencia a la raíz del objeto JSON serializado.

Figura 2 Resultados de una consulta JSON

Dado que la columna JSON está configurada como una columna NVARCHAR sin formato, puede usar la función ISJSON para comprobar si el contenido de la columna es JSON real. La función devuelve un valor positivo si el contenido es JSON.

JSON_VALUE siempre devuelve una cadena de hasta 4000 bytes, independientemente de la propiedad seleccionada. Si espera un valor devuelto más largo, debe usar OPENJSON en su lugar. Puede que quiera considerar a cualquier precio una función CAST para obtener un valor del tipo correcto. Volviendo al ejemplo anterior, supongamos que quiere que el número de habitantes de un país esté formateado con comas. (En general, no sería una buena idea, porque formatear datos en el nivel de presentación proporciona al código mucha más flexibilidad). La función SQL FORMAT espera recibir un número y usted recibe un error si pasa el valor JSON directo. Para que funcione, debe recurrir a una función CAST explícita:

SELECT CountryCode,
  CountryName,
  FORMAT(CAST(
    JSON_VALUE(Serialized, '$.Population') AS int), 'N0')
    AS People
FROM Countries
WHERE ISJSON(Serialized) > 0 AND
  JSON_VALUE(Serialized,'$.Population') > 100000000
ORDER BY JSON_VALUE(Serialized, '$.AreaInSqKm')

La función JSON_VALUE solo puede devolver un valor escalar único. Si tiene una matriz de un objeto anidado que quiere extraer, debe recurrir a la función JSON_QUERY.

¿Es eficaz realizar la consulta sobre datos JSON? Hagamos algunas pruebas.

Indexación de contenido JSON en SQL Server 2016

Aunque pueda parecer obvio, consultar la cadena JSON completa desde la base de datos y, luego analizarla en memoria por medio de una biblioteca dedicada, como Newtonsoft JSON, aunque siempre funcional, podría no ser un enfoque eficaz en todos los casos. La eficacia depende en gran medida del número de registros de la base de datos y de lo que pueda tardar realmente en obtener los datos necesarios con el formato necesario. Es probable que, en el caso de una consulta que su aplicación ejecute ocasionalmente, el procesamiento en memoria de datos JSON siga siendo una opción. Sin embargo, realizar la consulta a través de funciones dedicadas de JSON y permitir que SQL Server realice el análisis internamente suele tener como resultado un código ligeramente más rápido. La diferencia es aún mayor si se agrega un índice en los datos JSON.

No obstante, no debe crear el índice en la columna JSON, ya que el valor JSON se indexaría como una cadena única. Apenas consultará la cadena JSON completa o un subconjunto de esta. De manera más realista, consultará en su lugar el valor de una propiedad concreta en el objeto JSON serializado. En enfoque más eficaz es crear una o más columnas calculadas basadas en el valor de una o más propiedades JSON y, después, indexar esas columnas. Aquí se muestra un ejemplo en T-SQL:

-- Add a computed column
ALTER TABLE dbo.Countries
ADD JsonPopulation
AS JSON_VALUE(Serialized, '$.Population')
-- Create an index
CREATE INDEX IX_Countries_JsonPopulation
ON dbo.Countries(JsonPopulation)

De nuevo, debe ser consciente de que la función JSON_VALUE devuelve NVARCHAR, de modo que, salvo que agregue la función CAST, el índice se creará en el texto.

Resulta interesante que el análisis de JSON sea más rápido que la deserialización de algunos tipos especiales, como los datos XML y espaciales. Puede obtener más información en bit.ly/2kthrrC. En resumen, el análisis de JSON es mejor que la captura de propiedades de otros tipos.

JSON y EF

Como observación general, la compatibilidad de JSON en SQL Server 2016 se expone principalmente a través de la sintaxis T-SQL, ya que las herramientas son un poco limitadas en este momento. En concreto, EF no proporciona actualmente servicios para la consulta de datos JSON, a excepción del método SqlQuery en EF6 y de FromSql en EF Core. No obstante, esto no significa que no se puedan serializar propiedades complejas de las clases de C# (por ejemplo, matrices) en las columnas JSON. Existe un excelente tutorial de EF Core disponible en bit.ly/2kVEsam.

Resumen

SQL Server 2016 presenta algunas funcionalidades de JSON nativas, que le permitirán consultar de manera más eficaz los datos JSON almacenados como un conjunto de filas canónico. Esto sucede principalmente cuando los datos JSON son la versión serializada de algún agregado de datos semiestructurado. Los índices compilados a partir de columnas calculadas que reflejen ese valor de una o más propiedades JSON ayudan sin duda a mejorar el rendimiento.

Los datos JSON se almacenan como texto sin formato y no se consideran un tipo especial, como los datos XML y espaciales. No obstante, esto solo le permite usar las columnas JSON en los objetos de SQL Server que desee directamente. No se puede decir lo mismo de otros tipos complejos, como XML, CLR y espaciales, que permanecen en lista de espera.

En esta columna, me centré en el escenario de JSON a conjunto de filas. Sin embargo, SQL Server 2016 también es totalmente compatible con el escenario de consulta de conjunto de filas a JSON al escribir una consulta T-SQL regular y, después, asignar los resultados a objetos JSON por medio de la cláusula FOR JSON. Para obtener más información sobre esta característica, consulte bit.ly/2fTKly7.

Dino Esposito es el autor de "Microsoft .NET: Architecting Applications for the Enterprise" (Microsoft Press, 2014) y "Modern Web Applications with ASP.NET" (Microsoft Press, 2016). Como experto técnico para las plataformas .NET y Android en JetBrains y orador frecuente en eventos internacionales del sector, Esposito comparte su visión sobre el software en software2cents.wordpress.com y en su Twitter @despos.

Gracias al siguiente experto técnico de Microsoft por revisar este artículo: Jovan Popovic

Compartir a través de