Indexar propiedades JSON tipadas en SQL Server

Guardar JSON resulta cómodo cuando una integración envía atributos variables. El problema aparece cuando un filtro frecuente analiza ese documento sobre muchas filas. Una columna calculada estrecha y tipada puede exponer la propiedad importante a un índice convencional conservando el contenido original.

Distingue atributos flexibles y claves del negocio. Si la identidad del cliente dirige uniones, autorización y consultas, necesita un contrato explícito. JSON no elimina la necesidad de definir tipo, presencia obligatoria y rango.

Extraer el tipo previsto

El ejemplo utiliza funciones JSON disponibles desde SQL Server 2016 y crea objetos permanentes solo en una base de práctica.

-- Create in a disposable practice database.
CREATE TABLE dbo.JsonOrderDemo (
    DocumentId int NOT NULL PRIMARY KEY,
    Payload nvarchar(max) NOT NULL,
    CustomerId AS TRY_CONVERT(bigint, JSON_VALUE(Payload, '$.customerId')) PERSISTED,
    CONSTRAINT CK_JsonOrderDemo_Json CHECK (ISJSON(Payload) = 1),
    CONSTRAINT CK_JsonOrderDemo_Customer CHECK (CustomerId IS NOT NULL AND CustomerId > 0)
);
CREATE INDEX IX_JsonOrderDemo_Customer ON dbo.JsonOrderDemo(CustomerId);
INSERT dbo.JsonOrderDemo(DocumentId, Payload) VALUES
(1, N'{"customerId":42,"status":"new"}'),
(2, N'{"customerId":43,"status":"new"}'),
(3, N'{"customerId":42,"status":"paid"}');
SELECT DocumentId FROM dbo.JsonOrderDemo WHERE CustomerId = 42;

La búsqueda inicial devuelve documentos 1 y 3. CustomerId se deriva como bigint, por lo que el índice almacena claves numéricas en lugar de textos largos. En una tabla diminuta, el optimizador puede preferir un scan porque cuesta menos. Se crea un acceso posible, no un plan forzado.

TRY_CONVERT devuelve NULL para valores no convertibles. El segundo CHECK rechaza explícitamente NULL y números no positivos. Comprobar únicamente CustomerId > 0 permitiría UNKNOWN para NULL y no exigiría presencia.

ISJSON comprueba sintaxis, no todo el esquema del negocio. Un array válido o un objeto sin customerId no constituye una orden válida según este contrato. La columna calculada detecta la clave numérica ausente, pero otros atributos necesitan validación propia.

La conversión admite tanto números JSON como cadenas numéricas convertibles. Si la API debe distinguirlos, valida el tipo de token, por ejemplo mediante OPENJSON. No dejes esa diferencia a una conversión accidental.

Mantener coherentes extracción y consulta

Consultar CustomerId directamente aclara el acceso y el tipo de parámetros. SQL Server puede reconocer algunas expresiones calculadas equivalentes, pero pequeñas diferencias pueden cambiar esa posibilidad. Es preferible un contrato estable a pedir a cada consumidor que reconstruya la expresión.

Los nombres de propiedades JSON distinguen mayúsculas en las rutas. customerId y CustomerId no son equivalentes aunque la intercalación ignore mayúsculas en comparaciones ordinarias. Prueba las convenciones de los serializadores participantes.

JSON_VALUE tiene límites propios. Con la ruta nvarchar tradicional, un escalar mayor de 4.000 caracteres puede devolver NULL en modo lax o error en strict. No sirve para extraer descripciones ilimitadas. OPENJSON puede exponer escalares grandes mediante un esquema adecuado.

Evita indexar nvarchar(4000) para un dato realmente numérico o un código corto. Siguen existiendo límites de clave y costes de comparación. En sentido contrario, convertir texto a una longitud arbitrariamente pequeña puede truncar información; valida antes esa longitud.

Considerar escrituras y cambios de esquema

Modificar el documento recalcula la propiedad y mantiene el índice.

UPDATE dbo.JsonOrderDemo
SET Payload = JSON_MODIFY(Payload, '$.customerId', 43)
WHERE DocumentId = 1;
SELECT DocumentId, CustomerId FROM dbo.JsonOrderDemo ORDER BY DocumentId;

El documento 1 pasa al cliente 43. La aplicación no necesita actualizar por separado una columna espejo. Se elimina un riesgo de inconsistencia, pero análisis, restricciones, almacenamiento persistido y mantenimiento del índice siguen costando.

PERSISTED es una decisión, no un requisito universal de todos los índices calculados. Determinismo, precisión, tipos y opciones SET deben satisfacer las reglas de SQL Server. Compara espacio y sobrecoste de escritura con las lecturas ahorradas.

Renombrar customerId o cambiar su tipo sigue siendo una migración de esquema aunque el documento sea flexible. Coordina productor, validación, extracción, filas existentes y lectores. Un despliegue no debería convertir propiedades antiguas en NULL silenciosamente ni rechazar escrituras previstas.

Prueba claves ausentes, mayúsculas incorrectas, arrays, JSON mal formado, valores no numéricos, desbordamientos y actualizaciones normales. Mide consultas representativas e ingestión. El índice aporta valor cuando su significado permanece correcto mientras evolucionan los documentos.

Referencias técnicas: Microsoft Learn: Index JSON data · Microsoft Learn: JSON_VALUE · Microsoft Learn: ISJSON.