Procesamiento de datos JSON con funciones integradas
Considere un escenario en el que la aplicación de comercio electrónico almacena las preferencias del cliente y los metadatos de pedidos como documentos JSON. Una aplicación móvil envía datos del carro de la compra en formato JSON y el sistema de informes debe exportar catálogos de productos como JSON para una API web. Trabajar directamente con JSON en la base de datos elimina la necesidad de transformaciones de capa de aplicación y mantiene el procesamiento eficaz de los datos.
Las bases de datos SQL Server, Azure SQL y SQL de Fabric proporcionan compatibilidad integrada con JSON que permite analizar, consultar, crear y transformar datos JSON directamente en T-SQL. En esta unidad, aprenderá a usar funciones JSON para extraer valores, construir la salida JSON, agregar datos en matrices JSON y validar el contenido JSON.
Extracción de valores con JSON_VALUE y JSON_QUERY
Al trabajar con JSON almacenado en la base de datos, debe extraer valores específicos para filtrar, combinar o mostrar. SQL Server proporciona dos funciones para este propósito:
JSON_VALUE() extrae un valor escalar (cadena, número, booleano) de una cadena JSON:
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd",
"active": true
},
"orderTotal": 1599.99
}';
SELECT
JSON_VALUE(@json, '$.customer.id') AS CustomerID,
JSON_VALUE(@json, '$.customer.name') AS CustomerName,
JSON_VALUE(@json, '$.orderTotal') AS OrderTotal;
El conjunto de resultados será:
CustomerID CustomerName OrderTotal
---------- ------------ ----------
12345 Contoso Ltd 1599.99
La función navega por la estructura JSON mediante la expresión path y devuelve el valor como una NVARCHAR(4000) cadena. Puede convertir el resultado a otros tipos de datos según sea necesario para los cálculos o comparaciones.
JSON_QUERY() extrae un objeto JSON o una matriz (valores no escalados):
DECLARE @json NVARCHAR(MAX) = N'{
"customer": {
"id": 12345,
"name": "Contoso Ltd"
},
"items": [
{"product": "Widget", "qty": 5},
{"product": "Gadget", "qty": 3}
]
}';
SELECT
JSON_QUERY(@json, '$.customer') AS CustomerObject,
JSON_QUERY(@json, '$.items') AS ItemsArray;
El conjunto de resultados será:
CustomerObject ItemsArray
-------------------------------------- ------------------------------------------------
{"id": 12345,"name": "Contoso Ltd"} [{"product": "Widget", "qty": 5},{"product": "Gadget", "qty": 3}]
A diferencia JSON_VALUE()de , JSON_QUERY() conserva la estructura JSON, devolviendo objetos y matrices como cadenas JSON válidas que puede almacenar, pasar a otras funciones o volver a las aplicaciones.
La expresión de ruta de acceso usa $ para representar el elemento raíz, con notación de puntos para las propiedades anidadas y notación de corchetes para los elementos de matriz, como en el ejemplo siguiente:
-- Access array elements by index (0-based)
SELECT JSON_VALUE(@json, '$.items[0].product') AS FirstProduct;
El resultado será:
FirstProduct
------------
Widget
Los índices de matriz comienzan en 0, por lo que $.items[0] hace referencia al primer elemento. Use esta sintaxis para extraer elementos específicos cuando conozca la posición o combine con OPENJSON cuando necesite procesar todos los elementos de matriz.
Sugerencia
Use JSON_VALUE() cuando necesite un valor escalar para comparaciones o cálculos. Use JSON_QUERY() cuando necesite conservar la estructura JSON de objetos o matrices anidados.
Análisis de matrices JSON con OPENJSON
OPENJSON es una función con valores de tabla que transforma los datos JSON en un conjunto de filas relacional. Use esta función para combinar datos JSON con tablas relacionales o procesar elementos de matriz individualmente.
La consulta siguiente analiza una matriz JSON en filas con el esquema predeterminado:
DECLARE @json NVARCHAR(MAX) = N'[
{"id": 1, "name": "Widget", "price": 29.99},
{"id": 2, "name": "Gadget", "price": 49.99},
{"id": 3, "name": "Gizmo", "price": 19.99}
]';
SELECT * FROM OPENJSON(@json);
El conjunto de resultados será:
key value type
--- -------------------------------------------- ----
0 {"id": 1, "name": "Widget", "price": 29.99} 5
1 {"id": 2, "name": "Gadget", "price": 49.99} 5
2 {"id": 3, "name": "Gizmo", "price": 19.99} 5
Sin un esquema, OPENJSON devuelve tres columnas: key (el índice de matriz o el nombre de propiedad), value (el contenido JSON) y type (un número que indica el tipo de datos JSON: 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object).
La consulta siguiente define un esquema explícito para extraer columnas específicas con tipos de datos adecuados:
SELECT
ProductID,
ProductName,
Price
FROM OPENJSON(@json)
WITH (
ProductID INT '$.id',
ProductName NVARCHAR(100) '$.name',
Price DECIMAL(10,2) '$.price'
);
El conjunto de resultados será:
ProductID ProductName Price
--------- ----------- ------
1 Widget 29.99
2 Gadget 49.99
3 Gizmo 19.99
La cláusula WITH asigna propiedades JSON a columnas tipadas. Este enfoque proporciona tipos de datos adecuados para cálculos y comparaciones, y le permite seleccionar solo las propiedades que necesita.
Combinar OPENJSON con datos de tabla mediante CROSS APPLY:
-- Assuming Orders table has a JSON column called OrderDetails
SELECT
o.OrderID,
o.CustomerID,
items.ProductName,
items.Quantity,
items.UnitPrice
FROM Orders AS o
CROSS APPLY OPENJSON(o.OrderDetails)
WITH (
ProductName NVARCHAR(100) '$.product',
Quantity INT '$.qty',
UnitPrice DECIMAL(10,2) '$.price'
) AS items;
Nota:
Cuando se usa OPENJSON con CROSS APPLY, las filas de la tabla principal que tienen NULL o los valores JSON vacíos no aparecen en los resultados. Use OUTER APPLY si necesita incluir filas sin datos JSON.
Construcción de JSON con JSON_OBJECT y JSON_ARRAY
SQL Server 2022 introdujo JSON_OBJECT funciones y JSON_ARRAY para la construcción intuitiva de JSON:
JSON_OBJECT() crea un objeto JSON a partir de pares clave-valor; en el ejemplo siguiente se muestra cómo compilar un objeto JSON para un producto:
SELECT JSON_OBJECT(
'id': ProductID,
'name': Name,
'price': ListPrice,
'available': CASE WHEN SellEndDate IS NULL THEN 'true' ELSE 'false' END
) AS ProductJson
FROM SalesLT.Product
WHERE ProductID = 680;
El resultado será:
ProductJson
---------------------------------------------------------------------------
{"id":680,"name":"HL Road Frame - Black, 58","price":1431.50,"available":"true"}
La función controla automáticamente la conversión de tipos de datos y el escape JSON adecuado para caracteres especiales en valores de cadena.
JSON_ARRAY() crea una matriz JSON a partir de valores; en el ejemplo siguiente se compila una matriz JSON:
SELECT JSON_ARRAY(
'SQL Server',
'Azure SQL Database',
'SQL Database in Fabric'
) AS Platforms;
El resultado será:
Platforms
---------------------------------------------------------
["SQL Server","Azure SQL Database","SQL Database in Fabric"]
Puede pasar valores de columna, variables o valores literales a JSON_ARRAY(). La función crea una matriz JSON con el formato correcto independientemente de los tipos de entrada.
A continuación, combine estas funciones para compilar estructuras JSON anidadas. En el ejemplo siguiente se crea un objeto JSON de pedido completo con información de clientes y totales:
SELECT JSON_OBJECT(
'orderId': soh.SalesOrderID,
'orderDate': soh.OrderDate,
'customer': JSON_OBJECT(
'id': c.CustomerID,
'name': c.CompanyName
),
'totals': JSON_OBJECT(
'subtotal': soh.SubTotal,
'tax': soh.TaxAmt,
'total': soh.TotalDue
)
) AS OrderJson
FROM SalesLT.SalesOrderHeader AS soh
INNER JOIN SalesLT.Customer AS c
ON soh.CustomerID = c.CustomerID
WHERE soh.SalesOrderID = 71774;
El resultado será:
OrderJson
--------------------------------------------------------------------------------
{"orderId":71774,"orderDate":"2008-06-01","customer":{"id":29825,"name":"Contoso"},"totals":{"subtotal":880.35,"tax":70.43,"total":972.79}}
Al anidar llamadas JSON_OBJECT, se crean estructuras jerárquicas que se ajustan al formato esperado de la aplicación. Este enfoque es más limpio que la concatenación de cadenas y garantiza una salida JSON válida.
Agregar datos con JSON_ARRAYAGG
JSON_ARRAYAGG agrega valores de varias filas a una sola matriz JSON. Esta función es útil para crear una salida JSON desnormalizada a partir de datos relacionales normalizados:
SELECT
c.CustomerID,
c.CompanyName,
JSON_ARRAYAGG(soh.SalesOrderID) AS OrderIds
FROM SalesLT.Customer AS c
INNER JOIN SalesLT.SalesOrderHeader AS soh
ON c.CustomerID = soh.CustomerID
GROUP BY c.CustomerID, c.CompanyName;
El resultado será:
CustomerID CompanyName OrderIds
---------- ------------------- ------------------
29825 Contoso Retail [71774,71776,71780]
29847 Adventure Works [71782,71784]
La función recopila todos los valores coincidentes de las filas agrupadas y los combina en una sola matriz JSON. Esto resulta útil para crear respuestas de API desnormalizadas a partir de tablas de base de datos normalizadas.
Puede combinar JSON_ARRAYAGG con JSON_OBJECT para crear matrices de objetos complejos:
SELECT
pc.Name AS Category,
JSON_ARRAYAGG(
JSON_OBJECT(
'id': p.ProductID,
'name': p.Name,
'price': p.ListPrice
)
) AS Products
FROM SalesLT.ProductCategory AS pc
INNER JOIN SalesLT.Product AS p
ON pc.ProductCategoryID = p.ProductCategoryID
GROUP BY pc.ProductCategoryID, pc.Name;
El resultado siguiente será:
Category Products
-------------- --------------------------------------------------------------------------
Road Bikes [{"id":749,"name":"Road-150 Red, 62","price":3578.27},{"id":750,"name":"Road-150 Red, 44","price":3578.27}]
Mountain Bikes [{"id":771,"name":"Mountain-100 Silver, 38","price":3399.99},{"id":772,"name":"Mountain-100 Black, 38","price":3374.99}]
Importante
JSON_ARRAYAGG y JSON_OBJECT/JSON_ARRAY las funciones están disponibles en SQL Server 2022 y versiones posteriores, Azure SQL Database y bases de datos SQL en Microsoft Fabric. Para versiones anteriores, use FOR JSON PATH para una funcionalidad similar.
Validación y comprobación de JSON con JSON_CONTAINS
Los datos JSON de orígenes externos pueden tener un formato incorrecto, faltan propiedades esperadas o contienen valores inesperados. Si intenta extraer valores de JSON inválido o de rutas de acceso faltantes, esto puede causar fallos en la consulta o devolver resultados NULL engañosos que enmascaran problemas de datos.
El procesamiento de JSON sólido requiere codificación defensiva: valide que el JSON está bien formado antes de analizarlo, compruebe que existen rutas de acceso esperadas antes de extraer valores y compruebe que los valores coinciden con sus expectativas antes de usarlos en la lógica de negocios. SQL Server proporciona varias funciones para ayudarle a validar el contenido JSON en cada fase de procesamiento.
Descripción de los modos de ruta de acceso lax frente a strict
Puede usar expresiones de ruta de acceso JSON en dos modos que gestionan el manejo de errores:
DECLARE @json NVARCHAR(MAX) = N'{"name": "Widget", "price": 29.99}';
-- Lax mode (default): Returns NULL for missing paths
SELECT JSON_VALUE(@json, 'lax $.description') AS LaxResult;
-- Strict mode: Raises an error for missing paths
SELECT JSON_VALUE(@json, 'strict $.description') AS StrictResult;
El resultado será:
LaxResult
---------
NULL
-- Strict mode raises: Property cannot be found on the specified JSON path.
Use el modo lax (el valor predeterminado) cuando se espera que falten propiedades y se debería devolver NULL. Utilice el modo strict cuando la falta de propiedades indique un problema de datos que debe generar un error.
ISJSON valida si una cadena contiene json válido. En el siguiente ejemplo se muestra cómo usar ISJSON:
SELECT
ISJSON('{"name": "test"}') AS ValidJson, -- Returns 1
ISJSON('not valid json') AS InvalidJson, -- Returns 0
ISJSON(NULL) AS NullJson; -- Returns NULL
El resultado será:
ValidJson InvalidJson NullJson
--------- ----------- --------
1 0 NULL
Utilice ISJSON en WHERE cláusulas para filtrar filas con JSON válido, o en CASE expresiones para manejar datos no válidos elegantemente.
JSON_PATH_EXISTS comprueba si existe una ruta de acceso específica en un documento JSON, como en el ejemplo siguiente:
DECLARE @json NVARCHAR(MAX) = N'{"customer": {"name": "Contoso", "tier": "Gold"}}';
SELECT
JSON_PATH_EXISTS(@json, '$.customer.name') AS HasName,
JSON_PATH_EXISTS(@json, '$.customer.email') AS HasEmail;
El resultado será:
HasName HasEmail
------- --------
1 0
Esta función devuelve 1 si existe la ruta de acceso, 0 si no lo hace. Úselo antes de llamar JSON_VALUE en modo estricto o para procesar condicionalmente JSON con estructuras variables.
Use JSON_CONTAINS para comprobar si un documento JSON contiene un valor o objeto específico, como en el ejemplo siguiente:
DECLARE @json NVARCHAR(MAX) = N'{"tags": ["sql", "database", "azure"]}';
SELECT
JSON_CONTAINS(@json, '"sql"', '$.tags') AS HasSqlTag,
JSON_CONTAINS(@json, '"python"', '$.tags') AS HasPythonTag;
El resultado será:
HasSqlTag HasPythonTag
--------- ------------
1 0
Optimización de consultas JSON con columnas calculadas
Al consultar con frecuencia propiedades JSON específicas, el motor de base de datos debe analizar el documento JSON para cada fila de cada consulta. En el caso de las tablas con miles o millones de filas, este análisis repetido crea una sobrecarga significativa. Las columnas calculadas permiten extraer valores JSON una vez y almacenarlos en un formato consultable que admita la indexación.
Por qué el análisis de JSON afecta al rendimiento
Considere una tabla con 100 000 registros de producto donde cada fila contiene un documento JSON con atributos de producto. Un filtrado de consultas por categoría debe:
- Leer cada fila de la tabla
- Análisis del documento JSON para encontrar la propiedad category
- Extracción y comparación del valor
Sin optimización, incluso los filtros simples requieren escaneos completos de tabla con análisis de JSON en cada una de las filas.
Creación de columnas calculadas para propiedades JSON
Una columna calculada extrae automáticamente una propiedad JSON y la pone a disposición como una columna normal, como en el ejemplo siguiente:
-- Add a computed column that extracts a JSON property
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category');
-- The column is now available in queries
SELECT ProductID, ProductName, ProductCategory
FROM Products
WHERE ProductCategory = 'Electronics';
El resultado será:
ProductID ProductName ProductCategory
--------- ------------------- ---------------
101 Wireless Mouse Electronics
102 USB Keyboard Electronics
103 HD Monitor Electronics
De forma predeterminada, las columnas calculadas son virtuales. La base de datos calcula el valor en tiempo de consulta, pero puede optimizar la extracción de JSON. Para mejorar aún mejor el rendimiento, puede conservar la columna calculada como en el ejemplo siguiente:
-- Persisted computed column stores the extracted value physically
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED;
Las columnas persistentes almacenan el valor extraído en el disco, por lo que el JSON solo se analiza durante INSERT las operaciones y UPDATE , no durante SELECT las consultas.
Adición de índices para un filtrado más rápido
La ganancia de rendimiento real procede de la indexación de columnas calculadas:
-- Create an index on the computed column
CREATE INDEX IX_Products_Category ON Products(ProductCategory);
-- Now this query uses an index seek instead of a table scan
SELECT ProductID, ProductName
FROM Products
WHERE ProductCategory = 'Electronics';
Sin el índice, la consulta examina todas las 100 000 filas. Con el índice, el motor de consultas realiza una búsqueda de índice y recupera solo las filas coincidentes. Esto puede reducir el tiempo de consulta de segundos a milisegundos.
Indexación de varias propiedades JSON
Para las consultas que filtran por varias propiedades JSON, cree columnas calculadas y un índice compuesto:
-- Extract multiple properties
ALTER TABLE Products
ADD ProductCategory AS JSON_VALUE(ProductData, '$.category') PERSISTED,
ProductBrand AS JSON_VALUE(ProductData, '$.brand') PERSISTED,
ProductPrice AS CAST(JSON_VALUE(ProductData, '$.price') AS DECIMAL(10,2)) PERSISTED;
-- Create a composite index for common query patterns
CREATE INDEX IX_Products_Category_Brand ON Products(ProductCategory, ProductBrand);
-- Create an index for price range queries
CREATE INDEX IX_Products_Price ON Products(ProductPrice);
Ahora, las consultas pueden filtrarse por categoría y marca, u ordenarse por precio, utilizando estos índices de forma eficaz.
Sugerencia
Para las propiedades JSON a las que se accede con frecuencia, las columnas calculadas con índices pueden mejorar el rendimiento de las consultas en comparación con el análisis de JSON en el momento de la consulta. Monitorice los patrones de consulta y cree columnas calculadas para las propiedades usadas en las cláusulas WHERE, JOIN o ORDER BY.
Transformación de datos relacionales en JSON con FOR JSON
Para obtener una salida JSON completa de las consultas, use FOR JSON PATH o FOR JSON AUTO:
SELECT
p.ProductID,
p.Name,
p.ListPrice,
pc.Name AS CategoryName
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ListPrice > 1000
FOR JSON PATH, ROOT('products');
El resultado será:
{"products":[{"ProductID":749,"Name":"Road-150 Red, 62","ListPrice":3578.27,"CategoryName":"Road Bikes"},{"ProductID":750,"Name":"Road-150 Red, 44","ListPrice":3578.27,"CategoryName":"Road Bikes"}]}
FOR JSON PATH proporciona control sobre la estructura JSON a través de alias de columna. Use la notación de puntos en alias para crear objetos anidados:
SELECT
p.ProductID AS 'product.id',
p.Name AS 'product.name',
pc.Name AS 'product.category'
FROM SalesLT.Product AS p
INNER JOIN SalesLT.ProductCategory AS pc
ON p.ProductCategoryID = pc.ProductCategoryID
WHERE p.ProductID = 680
FOR JSON PATH;
El resultado será:
[{"product":{"id":680,"name":"HL Road Frame - Black, 58","category":"Road Frames"}}]
El alias de la columna 'product.id' crea un objeto anidado product con una propiedad id. Esta técnica le permite dar forma a la salida para que coincida con el formato esperado de la API sin procesamiento posterior.
Para obtener más información sobre las funciones JSON en SQL Server, consulte Datos JSON en SQL Server y funciones JSON.