Compartir a través de

Almacenamiento de documentos JSON en SQL Server o SQL Database

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed Instance

La SQL Database Engine cuenta con funciones JSON nativas que permiten analizar documentos JSON con el lenguaje SQL estándar. Ahora puede almacenar documentos JSON en SQL Server o SQL Database y consultar datos JSON como en una base de datos NoSQL. En este artículo se describen las opciones para almacenar documentos JSON.

Formato de almacenamiento JSON

La primera decisión sobre el diseño del almacenamiento es cómo almacenar documentos JSON en las tablas. Hay dos opciones disponibles:

  • Almacenamiento de LOB: los documentos JSON se pueden almacenar tal cual en las columnas con los tipos de datos json o nvarchar. Esta es la mejor manera para realizar la ingesta y carga rápida de datos, porque la velocidad de carga coincide con la de las columnas de cadena. Este enfoque podría significar una reducción adicional del rendimiento en tiempo de consulta/análisis si no se realiza la indexación en valores JSON, porque los documenots JSON sin formato se deben analizar mientras se ejecutan las consultas.

  • Almacenamiento relacional: los documentos JSON se pueden analizar mientras se insertan en la tabla con las funciones OPENJSON, JSON_VALUE o JSON_QUERY. Es posible almacenar fragmentos de los documentos JSON de entrada en las columnas que contienen subelementos JSON con tipos de datos json o nvarchar. Este enfoque aumenta el tiempo de carga, porque el análisis de JSON se realiza durante la carga. Sin embargo, las consultas coinciden con el rendimiento de las consultas clásicas en los datos relacionales.

  • Actualmente en SQL Server, JSON no es un tipo de datos integrado.

  • Actualmente, el tipo de datos JSON está disponible en la base de datos de Azure SQL.

Tablas clásicas

La manera más sencilla para almacenar documentos JSON en SQL Server o Azure SQL Database es crear una tabla de dos columnas que contenga el id. del documento y el contenido de este. Por ejemplo:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max)
);

O bien, donde se admite:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] json
);

Esta estructura es equivalente a las colecciones que se pueden encontrar en las bases de datos de documento clásicas. La clave principal _id es un valor de incremento automático que proporciona un identificador único para cada documento y habilita las búsquedas rápidas. Esta estructura es una buena elección para los escenarios NoSQL clásicos en los que quiera recuperar un documento mediante su id. o actualizar un documento almacenado mediante este.

  • Utilice el tipo de datos json nativo donde esté disponible para almacenar documentos JSON.
  • El tipo de datos nvarchar (max) permite almacenar documentos JSON de hasta 2 GB de tamaño. Aunque esté seguro de que los documentos JSON no ocupan más de 8 KB, le recomendamos que utilice nvarchar(4000) en lugar de nvarchar(max) por motivos de rendimiento.

En la tabla de ejemplo creada en el ejemplo anterior se supone que los documentos JSON válidos se almacenan en la columna log. Si quiere estar seguro de que el JSON válido se guarda en la columna log, puede agregar una restricción CHECK en la columna. Por ejemplo:

ALTER TABLE WebSite.Logs
    ADD CONSTRAINT [Log record should be formatted as JSON]
                   CHECK (ISJSON([log])=1)

Cada vez que alguien inserta o actualiza un documento en la tabla, esta restricción comprueba que el documento JSON tiene un formato correcto. Sin la restricción, la tabla está optimizada para inserciones, porque cualquier documento JSON se agrega directamente a la columna sin procesarse.

Al almacenar los documentos JSON en la tabla, puede usar lenguaje Transact-SQL estándar para realizar consultas a los documentos. Por ejemplo:

SELECT TOP 100 JSON_VALUE([log], '$.severity'), AVG( CAST( JSON_VALUE([log],'$.duration') as float))
 FROM WebSite.Logs
 WHERE CAST( JSON_VALUE([log],'$.date') as datetime) > @datetime
 GROUP BY JSON_VALUE([log], '$.severity')
 HAVING AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) > 100
 ORDER BY AVG( CAST( JSON_VALUE([log],'$.duration') as float) ) DESC

El poder usar cualquier función de T-SQL y realizar consultas a cláusulas para realizar consultas a documentos JSON es una gran ventaja. SQL Server y SQL Database no introducen ninguna restricción en las consultas que se pueden usar para analizar documentos JSON. Puede extraer los valores de un documento JSON con la función JSON_VALUE y usarlos en la consulta como cualquier otro valor.

Esta posibilidad de poder usar sintaxis de consulta T-SQL enriquecida es la diferencia principal entre SQL Server y SQL Database, y las bases de datos NoSQL clásicas: en Transact-SQL seguramente tenga alguna función que puede necesitar para procesar datos JSON.

Índices

Si descubre que las consultas buscan con frecuencia documentos por alguna propiedad (por ejemplo, una propiedad severity en un documento JSON), puede agregar un índice no agrupado de almacén en filas a la propiedad para acelerar las consultas.

Puede crear una columna calculada que exponga valores JSON de las columnas JSON en la ruta de acceso especificada (es decir, en la ruta de acceso $.severity) y crear un índice estándar en esta columna calculada. Por ejemplo:

create table WebSite.Logs (
    [_id] bigint primary key identity,
    [log] nvarchar(max),
    [severity] AS JSON_VALUE([log], '$.severity'),
    index ix_severity (severity)
);

La columna calculada usada en este ejemplo es una columna virtual o no persistente que no agrega espacio adicional a la tabla. La usa el índice ix_severity para mejorar el rendimiento de las consultas como en el siguiente ejemplo:

SELECT [log]
FROM Website.Logs
WHERE JSON_VALUE([log], '$.severity') = 'P4'

Una característica importante de este índice es que es compatible con la intercalación. Si la columna nvarchar original tiene una propiedad COLLATION (por ejemplo, idioma japonés o diferenciación entre mayúsculas y minúsculas), el índice se organiza según las reglas del idioma o las de distinción entre mayúsculas y minúsculas asociadas con la columna nvarchar. Este reconocimiento de intercalaciones puede ser una característica importante si está desarrollando aplicaciones para mercados globales que necesiten utilizar reglas de idioma personalizadas al procesar documentos JSON.

Formato de almacén de columnas y tablas grandes

Si espera tener una gran cantidad de documentos JSON en la colección, le recomendamos que agregue un índice de almacén de columnas agrupadas a la colección, tal y como se muestra en el siguiente ejemplo:

create sequence WebSite.LogID as bigint;
go
create table WebSite.Logs (
    [_id] bigint default(next value for WebSite.LogID),
    [log] json,
    INDEX cci CLUSTERED COLUMNSTORE
);

Un índice de almacén de columnas agrupadas proporciona alta compresión de datos (hasta 25 veces), lo que puede reducir significativamente los requisitos de espacio de almacenamiento, reducir el coste de este y aumentar el rendimiento de E/S de la carga de trabajo. Además, los índices de almacén de columnas agrupadas están optimizados para análisis y recorridos de tablas en los documentos JSON, por lo que este tipo de índice puede ser la mejor opción para registrar análisis.

En el ejemplo anterior se usa un objeto de secuencia para asignar valores a la columna _id. Tanto las secuencias como las identidades son opciones válidas para la columna ID.

Tablas optimizadas para memoria y documentos que cambian con frecuencia

Si espera que se realice un gran número de operaciones de actualización, inserción y eliminación en las colecciones, puede almacenar los documentos JSON en tablas optimizadas para memoria. Las colecciones JSON optimizadas para memoria siempre mantienen los datos en la memoria, por lo que no se producen sobrecargas en la E/S de almacenamiento. Además, las colecciones JSON optimizadas para memoria no tienen ningún tipo de bloqueo, por lo que las acciones de los documentos no bloquearán ninguna otra operación.

Lo único que puede hacer para convertir una colección clásica en una colección optimizada para memoria es especificar la opción WITH (MEMORY_OPTIMIZED=ON) después de la definición de tabla, tal y como se muestra en el ejemplo siguiente. Así conseguirá una versión optimizada para memoria de la colección JSON.

CREATE TABLE WebSite.Logs (
  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json
) WITH (MEMORY_OPTIMIZED=ON)

Una tabla optimizada para memoria es la mejor opción para documentos que sufren cambios frecuentes. En los casos de tablas optimizadas para memoria, también es importante tener en cuenta el rendimiento. Utilice nvarchar(4000) en vez de nvarchar(max) para los documentos JSON en las colecciones optimizadas para memoria, si es posible, ya que puede aumentar increíblemente el rendimiento. El tipo de datos json no se admite con tablas optimizadas para memoria.

Como sucede con las tablas clásicas, puede agregar índices en los campos que expone en tablas optimizadas para memoria con columnas calculadas. Por ejemplo:

CREATE TABLE WebSite.Logs (

  [_id] bigint IDENTITY PRIMARY KEY NONCLUSTERED,
  [log] json,

  [severity] AS cast(JSON_VALUE([log], '$.severity') as tinyint) persisted,
  INDEX ix_severity (severity)

) WITH (MEMORY_OPTIMIZED=ON)

Para maximizar el rendimiento, convierta el valor JSON en el tipo más pequeño posible que pueda usarse para retener el valor de la propiedad. En el ejemplo anterior, se usa tinyint.

También puede colocar consultas de SQL que actualicen los documentos JSON en procedimientos almacenados para obtener las ventajas de la compilación nativa. Por ejemplo:

CREATE PROCEDURE WebSite.UpdateData(@Id int, @Property nvarchar(100), @Value nvarchar(100))
WITH SCHEMABINDING, NATIVE_COMPILATION

AS BEGIN
    ATOMIC WITH (transaction isolation level = snapshot,  language = N'English')

    UPDATE WebSite.Logs
    SET [log] = JSON_MODIFY([log], @Property, @Value)
    WHERE _id = @Id;

END

En este procedimiento compilado de forma nativa se toma la consulta y se crea código .DLL que ejecuta la consulta. Un procedimiento compilado de forma nativa es el enfoque más rápido para realizar consultas y actualizar datos.

Conclusión

Las funciones JSON nativas en SQL Server y SQL Database le permiten procesar documentos JSON como si fuesen bases de datos NoSQL. Las bases de datos (relacionales o NoSQL) tienen sus puntos a favor y en contra para el procesamiento de datos JSON. La ventaja principal del almacenamiento de documentos JSON en SQL Server o SQL Database es la compatibilidad completa con lenguaje SQL. Puede utilizar el lenguaje enriquecido Transact-SQL para procesar datos y configurar varias opciones de almacenamiento, desde índices de almacén de columnas para grandes compresiones y análisis rápidos a tablas optimizadas para memoria para procesamiento sin bloqueos. Al mismo tiempo, obtendrá la ventaja de las características de internacionalización y seguridad madura que puede usar de forma sencilla en su escenario NoSQL. Las razones que se describen en este artículo son excelentes y se deben tener en cuenta a la hora de almacenar documentos JSON en SQL Server o SQL Database.

Más información sobre JSON en SQL Server y Azure SQL Database

Para obtener una introducción visual a la compatibilidad integrada de JSON en SQL Server y Azure SQL Database, vea los siguientes vídeos:

Contenido relacionado