Administración de columnas e índices JSON

  • 8 minutos

Las bases de datos relacionales funcionan mejor cuando cada fila de una tabla tiene las mismas columnas. La estructura se define una vez y cada registro lo sigue. Este diseño funciona bien para datos como clientes, pedidos o facturas en los que los campos son predecibles. Pero algunos datos varían de registro a registro. Los atributos que necesita almacenar dependen del tipo de elemento, del origen de los datos o de las opciones realizadas por los usuarios. El diseño de tabla tradicional le obliga a crear muchas columnas vacías para la mayoría de las filas o dividir los datos entre muchas tablas. Las columnas JSON ofrecen otra opción: almacene las partes de variables como JSON mientras mantiene las partes predecibles en columnas normales.

Por ejemplo, un catálogo de productos de comercio electrónico tiene campos comunes, como el nombre del producto, el precio y la categoría que se aplican a cada artículo. Pero una camisa necesita tamaño y color, un portátil necesita velocidad de procesador y tamaño de pantalla, y un libro necesita autor y otros atributos. Con JSON, se almacenan los campos comunes como columnas y se colocan los atributos específicos de la categoría en una columna JSON. Puede agregar nuevos tipos de producto sin cambiar la estructura de la tabla.

Información sobre cuándo usar columnas JSON

Las columnas JSON permiten consultar e indexar datos semiestructurados mediante la sintaxis SQL conocida. No necesita una base de datos NoSQL independiente para controlar datos flexibles. Considere JSON para estos escenarios:

  • Preferencias de usuario: la configuración como el tema, el idioma y las opciones de notificación difieren por usuario y cambian a medida que se agregan características.
  • Respuestas de API : los datos de los servicios externos tienen estructuras anidadas que pueden cambiar cuando el proveedor actualiza su API.
  • Registros de auditoría : los registros que capturan antes y después de los estados deben adaptarse a medida que evolucionan los esquemas de tabla.
  • Aplicaciones multiinquilino : los distintos clientes requieren campos personalizados diferentes.
  • Metadatos flexibles : etiquetas, etiquetas y propiedades que varían según el registro y no se ajustan a un esquema fijo.

Creación y consulta de columnas JSON

SQL Server 2025 presenta un tipo de datos json nativo que almacena documentos JSON en un formato binario optimizado para consultar y manipular. El tipo nativo proporciona lecturas más eficaces (el documento ya se analiza), escrituras más eficaces (las actualizaciones pueden modificar valores individuales sin reescribir todo el documento) y una mejor compresión de almacenamiento en comparación con el almacenamiento de JSON como NVARCHAR(MAX).

En versiones anteriores de SQL Server, se almacena JSON en una NVARCHAR(MAX) columna.

Para leer valores de JSON, use funciones JSON como JSON_VALUE para extraer un valor único o JSON_QUERY para devolver un objeto o una matriz. Si consulta una propiedad JSON con frecuencia, puede crear un índice en una columna calculada que extraiga esa propiedad.

En el ejemplo siguiente se crea una tabla con una columna JSON, se insertan documentos, se consultan propiedades específicas, se actualizan los valores y se crea un índice en un campo al que se accede con frecuencia:

-- Create table with native JSON type (SQL Server 2025+)
CREATE TABLE ConfigurationData (
    ConfigID INT PRIMARY KEY,
    ConfigSettings JSON NOT NULL
);

-- Insert JSON documents
INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (1, '{"theme":"dark","language":"en","notifications":true}');

INSERT INTO ConfigurationData (ConfigID, ConfigSettings) 
VALUES (2, '{"theme":"light","language":"fr","notifications":false}');

-- Query JSON properties
SELECT ConfigID,
       JSON_VALUE(ConfigSettings, '$.theme') AS Theme,
       JSON_VALUE(ConfigSettings, '$.language') AS Language,
       JSON_QUERY(ConfigSettings, '$') AS FullConfig
FROM ConfigurationData;

-- Update a single property using the modify method (SQL Server 2025+ preview)
UPDATE ConfigurationData
SET ConfigSettings.modify('$.theme', 'light')
WHERE ConfigID = 1;

-- Alternative: JSON_MODIFY works with both JSON and NVARCHAR(MAX) columns
UPDATE ConfigurationData
SET ConfigSettings = JSON_MODIFY(CAST(ConfigSettings AS NVARCHAR(MAX)), '$.notifications', CAST(0 AS BIT))
WHERE ConfigID = 1;

-- Create index on frequently queried JSON property
ALTER TABLE ConfigurationData
ADD ThemeValue AS JSON_VALUE(ConfigSettings, '$.theme');

CREATE INDEX IX_Theme ON ConfigurationData(ThemeValue);

En este ejemplo se crea una tabla con una JSON columna que almacena las opciones de configuración del usuario. Las INSERT instrucciones agregan documentos JSON como literales de cadena. Para leer valores específicos, JSON_VALUE extrae valores escalares como el tema y el lenguaje, mientras JSON_QUERY que devuelve todo el objeto JSON. El .modify() método (actualmente en versión preliminar) actualiza una sola propiedad sin volver a escribir todo el documento. Dado que el json tipo no se puede usar como una columna de clave de índice, el ejemplo crea una columna calculada que extrae el valor del tema y, a continuación, indexa esa columna calculada.

Combinación de la estructura relacional y JSON

Las columnas JSON funcionan mejor para los datos que varían según el registro. Si cada fila tiene los mismos campos con tipos de datos coherentes, las columnas regulares son una mejor opción. Obtiene la validación de tipos de datos nativos, consultas más sencillas sin sintaxis de ruta de acceso JSON e indexación directa en columnas. Usa JSON para las partes de los datos que necesitan flexibilidad y mantén las partes predecibles en columnas tipificadas.

Puede combinar la estructura relacional con flexibilidad JSON para los productos que requieren metadatos variables. Este es un ejemplo:

-- Product with flexible metadata (SQL Server 2025+)
CREATE TABLE ProductMetadata (
    ProductID INT PRIMARY KEY,
    AdditionalAttributes JSON NOT NULL
        CHECK (JSON_PATH_EXISTS(AdditionalAttributes, '$.weight') = 1),
    FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
);

-- Store flexible product attributes
INSERT INTO ProductMetadata (ProductID, AdditionalAttributes) 
VALUES (1, '{"dimensions":{"length":10,"width":5,"height":8},"weight":2.5,"color":"blue"}');

-- Query nested JSON properties
SELECT ProductID,
       JSON_VALUE(AdditionalAttributes, '$.weight') AS Weight,
       JSON_VALUE(AdditionalAttributes, '$.dimensions.length') AS Length
FROM ProductMetadata;

Consideración de los principios de diseño JSON

Aplique estos principios al implementar columnas JSON:

  • Uso de JSON para datos semiestructurados : almacene estructuras de datos flexibles que varían según el registro, no los datos con esquemas coherentes.
  • Rutas de acceso consultadas con frecuencia : cree columnas calculadas con índices en propiedades JSON que consulte con frecuencia.
  • Validar las propiedades necesarias - use las CHECK restricciones con JSON_PATH_EXISTS para asegurarse de que los campos obligatorios están presentes.
  • Equilibrio de flexibilidad con estructura : mantenga los datos predecibles en columnas normales y use JSON solo para las partes variables.

Las columnas JSON proporcionan flexibilidad de esquema para los datos variables a la vez que mantienen las funcionalidades de consulta SQL, pero deben complementarse en lugar de reemplazar el diseño relacional de los datos estructurados.