Datos JSON en SQL Server

Artículo
08/20/2024

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

JSON es un formato de datos de texto muy popular que se usa para intercambiar datos en las aplicaciones web y móviles modernas. JSON también sirve para almacenar los datos no estructurados en archivos de registro o en bases de datos NoSQL, como Microsoft Azure Cosmos DB. Muchos servicios web REST devuelven resultados con formato de texto JSON o bien aceptan datos con este formato. Por ejemplo, la mayoría de los servicios de Azure, como Azure Search, Azure Storage y Azure Cosmos DB, cuentan con extremos REST que devuelven o usan JSON. JSON es también el formato principal para intercambiar datos entre páginas web y servidores web a través de llamadas AJAX.

Las funciones JSON, presentadas por primera vez en SQL Server 2016 (13.x), permiten combinar conceptos NoSQL y relacionales en la misma base de datos. Puedes combinar columnas relacionales clásicas con columnas que contienen documentos con formato de texto JSON en la misma tabla, analizar e importar documentos JSON en estructuras relacionales o dar formato de texto JSON a datos relacionales.

Nota:

La compatibilidad con JSON requiere el nivel de compatibilidad de la base de datos 130 o superior.

Este es un ejemplo de texto JSON:

[
    {
        "name": "John",
        "skills": [ "SQL", "C#", "Azure" ]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

SQL Server proporciona funciones y operadores integrados que permiten hacer lo siguiente con texto JSON:

Analizar texto JSON y leer o modificar valores.
Transformar matrices de objetos JSON a formato de tabla.
Ejecutar cualquier consulta de Transact-SQL en los objetos JSON convertidos.
Dar formato JSON a los resultados de consultas de Transact-SQL.

Diagrama que muestra la introducción a la compatibilidad integrada con JSON.

Funcionalidades clave de JSON de SQL Server y SQL Database

En las siguientes secciones se analizan las funcionalidades clave que proporciona SQL Server con su compatibilidad de JSON integrada.

Tipo de datos JSON

El nuevo tipo de datos json que almacena documentos JSON en un formato binario nativo que proporciona las siguientes ventajas sobre el almacenamiento de datos JSON en varchar/nvarchar:

Lecturas más eficaces, ya que el documento ya está analizado
Escrituras más eficaces, ya que la consulta puede actualizar valores individuales sin tener que acceder a todo el documento
Almacenamiento más eficaz, optimizado para compresión
Sin cambios en la compatibilidad con el código existente

Nota:

El tipo de datos JSON se encuentra actualmente en versión preliminar para Azure SQL Database y Azure SQL Instancia administrada (configurado con la directiva de actualización siempre actualizada).

El uso de las mismas funciones JSON descritas en este artículo sigue siendo la manera más eficaz de consultar el tipo de datos json. Para obtener más información sobre el tipo de datos json nativo, consulte Tipo de datos JSON.

Extraer valores de texto JSON y usarlos en consultas

Si tiene texto JSON almacenado en tablas de base de datos, puede usar las siguientes funciones integradas para leer o modificar los valores de ese texto JSON:

ISJSON (Transact-SQL) prueba si una cadena contiene un valor JSON válido.
JSON_VALUE (Transact-SQL) extrae un valor escalar de una cadena JSON.
JSON_QUERY (Transact-SQL) extrae un objeto o una matriz de una cadena JSON.
JSON_MODIFY (Transact-SQL) cambia un valor de una cadena JSON.

Ejemplo

En el siguiente ejemplo, la consulta usa datos tanto relacionales como JSON (almacenados en la columna denominada jsonCol) de una tabla denominada People:

SELECT Name,
    Surname,
    JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
    JSON_VALUE(jsonCol, '$.info.address."Address Line 1"')
        + ' ' + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
    JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
    AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
    AND STATUS = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Las herramientas y aplicaciones no distinguen entre los valores extraídos de las columnas de la tabla escalar y los valores de las columnas JSON. Puede usar los valores del texto JSON en cualquier parte de la consulta de Transact-SQL (incluidas las cláusulas WHERE, ORDER BY y GROUP BY, los agregados de ventanas, etc.). Las funciones JSON usan una sintaxis parecida a la de JavaScript para hacer referencia a los valores de texto JSON.

Para obtener más información, consulta Validar, consultar y cambiar datos JSON con funciones integradas (SQL Server), JSON_VALUE (Transact-SQL) y JSON_QUERY (Transact-SQL).

Cambiar valores de JSON

Si tiene que modificar partes del texto JSON, puede usar la función JSON_MODIFY (Transact-SQL) para actualizar el valor de una propiedad en una cadena JSON y devolver la cadena JSON actualizada. En el siguiente ejemplo se actualiza el valor de una propiedad de una variable que contiene JSON:

DECLARE @json NVARCHAR(MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

SELECT modifiedJson = @json;

Este es el conjunto de resultados.

{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

Convertir colecciones de JSON en un conjunto de filas

No es necesario un lenguaje de consulta personalizado para consultar JSON en SQL Server. Para consultar datos JSON, puede usar T-SQL estándar. Si tiene que crear una consulta o un informe sobre datos JSON, puede convertir los datos JSON fácilmente en filas y columnas llamando a la función de conjunto de filas OPENJSON. Para obtener más información, consulta Análisis y transformación de datos JSON con OPENJSON.

En el siguiente ejemplo, se llama a OPENJSON y se transforma la matriz de objetos almacenada en la variable @json en un conjunto de filas que se puede consultar con una instrucción estándar SELECT de SQL:

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
);

Este es el conjunto de resultados.

ID	firstName	lastName	age	dateOfBirth
2	John	Smith	25
5	Julia	Smith		2005-11-04T12:00:00

OPENJSON transforma la matriz de objetos JSON en una tabla, donde cada objeto se representa como una fila y los pares clave-valor se devuelven en forma de celdas. El resultado detecta las siguientes reglas:

OPENJSON convierte valores JSON a los tipos especificados en la cláusula WITH.
OPENJSON se puede encargar tanto de los pares clave-valor sin formato como de los objetos anidados organizados jerárquicamente.
No hay que devolver todos los campos incluidos en el texto JSON.
Si no existen valores JSON, OPENJSON devuelve valores NULL.
Opcionalmente, puede especificar una ruta de acceso después de la especificación de tipo para hacer referencia a una propiedad anidada o a una propiedad mediante un nombre diferente.
El prefijo opcional strict en la ruta de acceso señala que los valores de las propiedades especificadas deben existir en el texto JSON.

Para obtener más información, consulta Análisis y transformación de datos JSON con OPENJSON y OPENJSON (Transact-SQL).

Los documentos JSON podrían tener subelementos y datos jerárquicos que no se pueden asignar directamente a las columnas relacionales estándares. En este caso, es posible simplificar la jerarquía JSON mediante la combinación de la entidad primaria con submatrices.

En el ejemplo siguiente, el segundo objeto de la matriz tiene una submatriz que representa las aptitudes de una persona. Todos los objetos secundarios se pueden analizar mediante una llamada adicional a la función OPENJSON:

DECLARE @json NVARCHAR(MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
    firstName,
    lastName,
    age,
    dateOfBirth,
    skill
FROM OPENJSON(@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR(50) '$.info.name',
    lastName NVARCHAR(50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR(MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON(skills) WITH (skill NVARCHAR(8) '$');

La matriz skills se devuelve en la primera OPENJSON como el fragmento de texto JSON original y se pasa a otra función OPENJSON mediante el operador APPLY. La segunda función OPENJSON analizará la matriz JSON y los valores de cadena devueltos como un conjunto de filas de una columna única que se combinarán con el resultado de la primera OPENJSON.

Este es el conjunto de resultados.

ID	firstName	lastName	age	dateOfBirth	aptitudes
2	John	Smith	25
5	Julia	Smith		2005-11-04T12:00:00	SQL
5	Julia	Smith		2005-11-04T12:00:00	C#
5	Julia	Smith		2005-11-04T12:00:00	Azure

OUTER APPLY OPENJSON se une a la entidad de primer nivel con la submatriz y devolverá un conjunto de resultados sin formato. Debido a JOIN, la segunda fila se repite para cada aptitud.

Convertir datos de SQL Server a JSON o exportar JSON

Nota:

No se admite la conversión de datos de Azure Synapse Analytics a JSON ni la exportación de JSON.

Para dar formato JSON a los datos de SQL Server o a los resultados de las consultas, agregue la cláusula FOR JSON a una instrucción SELECT. Use FOR JSON para delegar en SQL Server la aplicación de formato de los resultados de JSON de las aplicaciones cliente. Para obtener más información, consulta Dar formato JSON a los resultados de consulta con FOR JSON.

En el siguiente ejemplo se usa el modo PATH con la cláusula FOR JSON:

SELECT id,
    firstName AS "info.name",
    lastName AS "info.surname",
    age,
    dateOfBirth AS dob
FROM People
FOR JSON PATH;

La cláusula FOR JSON cambia el formato de los resultados SQL a texto JSON que podrá usarse en cualquier aplicación que comprenda JSON. En la opción PATH se usan alias separados por puntos en la cláusula SELECT para anidar objetos en los resultados de la consulta.

Este es el conjunto de resultados.

[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]

Para obtener más información, consulta Dar formato JSON a los resultados de consulta con FOR JSON y FOR Clause (Transact-SQL).

Datos JSON de agregados

Las funciones de agregado JSON permiten la construcción de objetos o matrices JSON basados en un agregado de datos SQL.

JSON_OBJECTAGG construye un objeto JSON a partir de una agregación de datos o columnas SQL.
JSON_ARRAYAGG construye una matriz JSON a partir de una agregación de datos o columnas SQL.

Nota:

Tanto las funciones de agregado json como JSON_ARRAYAGG están actualmente en versión preliminar para Azure SQL Database y Azure SQL Instancia administrada (configuradas con la directiva de actualización siempre actualizada).JSON_OBJECTAGG

Casos de uso para datos JSON en SQL Server

La compatibilidad de JSON con SQL Server y Azure SQL Database le permite combinar conceptos relacionales y de NoSQL. Puede transformar fácilmente los datos semiestructurados en relacionales y viceversa. Sin embargo, JSON no es un reemplazo para los modelos relacionales existentes. Estos son algunos casos de uso específicos que se benefician de la compatibilidad de JSON en SQL Server y SQL Database.

Simplificación de modelos de datos complejos

Le recomendamos que desnormalice el modelo de datos con campos JSON en lugar de varias tablas secundarias.

Almacenamiento de datos de comercio electrónico y de venta al por menor

Almacene información sobre productos con una amplia gama de atributos variables en un modelo desnormalizado para obtener una mayor flexibilidad.

Procesamiento de datos de telemetría y registros

Cargue, consulte y analice datos de registro almacenados como archivos JSON con toda la potencia del lenguaje Transact-SQL.

Almacenamiento de datos de IoT semiestructurados

Si necesita un análisis en tiempo real de datos de IoT, cargue los datos entrantes directamente en la base de datos en lugar de almacenarlos provisionalmente en una ubicación de almacenamiento.

Simplificación del desarrollo de la API de REST

Transforme datos relacionales de la base de datos fácilmente en el formato JSON que usan las API de REST que admite su sitio web.

Combinar datos relacionales y datos JSON

SQL Server proporciona un modelo híbrido para almacenar y procesar datos JSON y relacionales usando el lenguaje Transact-SQL estándar. Puede organizar colecciones de documentos JSON en tablas, establecer relaciones entre ellas, combinar columnas escalares fuertemente tipadas y almacenadas en tablas con pares clave-valor flexibles almacenados en columnas JSON y, además, consultar valores JSON y escalares en una o varias tablas mediante Transact-SQL al completo.

El texto JSON se almacena en columnas VARCHAR o NVARCHAR y se indexa como texto sin formato. Cualquier componente o característica de SQL Server que admita texto admite JSON, por lo que no hay casi ninguna restricción en cuanto a la interacción entre JSON y otras características de SQL Server. Así, JSON puede almacenar en tablas temporales o tablas en memoria, aplicar predicados de seguridad de nivel de fila al texto JSON, etc.

Estos son algunos casos de uso que muestran cómo se puede usar la compatibilidad integrada de JSON en SQL Server.

Almacenamiento e índice de datos JSON en SQL Server

JSON es un formato de texto para que los documentos JSON se puedan almacenar en columnas NVARCHAR en una instancia de SQL Database. Como el tipo NVARCHAR es compatible con todos los subsistemas de SQL Server, puede colocar los documentos JSON en tablas con índices de almacén de columnas agrupados, tablas optimizadas para memoria o archivos externos que se pueden leer mediante OPENROWSET o PolyBase.

Para obtener más información sobre las opciones para almacenar, indexar y optimizar datos JSON en SQL Server, consulte los siguientes artículos:

Cargar archivos JSON en SQL Server

Puede dar formato a información que se almacena en archivos JSON estándar o JSON delimitado por línea. SQL Server puede importar el contenido de archivos JSON, analizarlo mediante las funciones OPENJSON o JSON_VALUE, y cargarlo en tablas.

Si los documentos JSON están almacenados en archivos locales, en unidades de red compartidas o en ubicaciones de Azure Files a los que SQL Server tenga acceso, puede realizar una operación de importación en bloque para cargar los datos JSON en SQL Server.
Si los archivos JSON delimitados por línea están almacenados en el sistema de archivos de Hadoop o en Azure Blob Storage, puede usar PolyBase para cargar el texto JSON, analizarlo en código Transact-SQL y cargarlo en tablas.

Importar datos JSON a tablas de SQL Server

Si tiene que cargar los datos JSON a SQL Server desde un servicio externo, puede usar OPENJSON para importar los datos a SQL Server en lugar de analizarlos en el nivel de aplicación.

En plataformas compatibles, utilice el tipo de datos json nativo en lugar de nvarchar(max) para mejorar el rendimiento y contar con un almacenamiento más eficaz.

DECLARE @jsonVariable NVARCHAR(MAX);

SET @jsonVariable = N'[
  {
    "Order": {
      "Number":"SO43659",
      "Date":"2011-05-31T00:00:00"
    },
    "AccountNumber":"AW29825",
    "Item": {
      "Price":2024.9940,
      "Quantity":1
    }
  },
  {
    "Order": {
      "Number":"SO43661",
      "Date":"2011-06-01T00:00:00"
    },
    "AccountNumber":"AW73565",
    "Item": {
      "Price":2024.9940,
      "Quantity":3
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON(@jsonVariable, N'$') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

Puede proporcionar el contenido de la variable JSON desde un servicio REST externo, enviarlo como un parámetro desde un marco de trabajo de JavaScript de cliente o bien cargarlo desde archivos externos. Los resultados de texto JSON se pueden insertar, actualizar o combinar fácilmente en tablas de SQL Server.

Analizar datos JSON con consultas SQL

Si tiene que filtrar o agregar datos JSON para elaborar informes, puede usar OPENJSON para transformar JSON en formato relacional. Luego puede usar las funciones integradas y Transact-SQL estándar para preparar los informes.

SELECT Tab.Id,
    SalesOrderJsonData.Customer,
    SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON(Tab.json, N'$.Orders.OrdersArray') WITH (
    Number VARCHAR(200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR(200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'),
    Tab.DateModified;

Puede usar tanto valores como columnas de tabla estándar de texto JSON en la misma consulta. Puede agregar índices a la expresión JSON_VALUE(Tab.json, '$.Status') para mejorar el rendimiento de la consulta. Para obtener más información, consulte Indexación de datos JSON.

Obtener datos de una tabla de SQL Server con formato JSON

Si tiene un servicio web que toma datos de la capa de base de datos y los devuelve en formato JSON, o si tiene bibliotecas o marcos de trabajo de JavaScript que aceptan datos con formato JSON, puede dar formato JSON a los resultados directamente en una consulta de SQL. En lugar de escribir código o incluir una biblioteca para convertir los resultados de consulta tabulares para, luego, serializar esos objetos en formato JSON, puede usar FOR JSON para delegar en SQL Server la aplicación de formato JSON.

Por ejemplo, imaginemos que quiere generar un resultado JSON que sea compatible con la especificación OData. El servicio web espera una solicitud y una respuesta en el siguiente formato:

Solicitud: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName
Respuesta: {"@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity", "ProductID": 1, "ProductName": "Chai"}

Esta dirección URL de OData representa una solicitud de las columnas ProductID y ProductName del producto con el ID 1. Puede usar FOR JSON para aplicar al resultado el formato que SQL Server espera.

SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',
  ProductID,
  Name as ProductName
FROM Production.Product
WHERE ProductID = 1
FOR JSON AUTO;

La salida de esta consulta es texto JSON que es totalmente compatible con la especificación de OData. SQL Server controla el formato y el escape. SQL Server también puede dar formato a resultados de consulta en cualquier formato, como JSON de OData o GeoJSON.

Probar la compatibilidad integrada de JSON con la base de datos de ejemplo de AdventureWorks

Para obtener la base de datos de ejemplo de AdventureWorks, descargue al menos el archivo de base de datos y el archivo de ejemplos y scripts en GitHub.

Después de restaurar la base de datos de ejemplo en una instancia de SQL Server, extraiga el archivo de ejemplos y abra el archivo JSON Sample Queries procedures views and indexes.sql desde la carpeta JSON. Ejecute los scripts de este archivo para cambiar el formato de algunos datos existentes a datos JSON, probar consultas de ejemplo e informes basados en los datos JSON, indexar los datos JSON e importar y exportar JSON.

Esto es lo que se puede hacer con los scripts incluidos en el archivo:

Desnormalizar el esquema existente para crear columnas de datos JSON.
- Almacene información de SalesReasons, SalesOrderDetails, SalesPerson, Customer y otras tablas que contengan información relacionada con el pedido de ventas en columnas JSON de la tabla SalesOrder_json.
- Almacene información de las tablas EmailAddresses y PersonPhone en la tabla Person_json como matrices de objetos JSON.
Crear procedimientos y vistas que consultan datos JSON.
Indexar datos JSON. Cree índices en propiedades JSON e índices de texto completo.
Importar y exportar JSON. Cree y ejecute procedimientos que exporten el contenido de las tablas Person y SalesOrder como resultados JSON, e importe y actualice las tablas Person y SalesOrder con entradas JSON.
Ejecutar ejemplos de consultas. Ejecute algunas consultas que llamen a las vistas y los procedimientos almacenados creados en los pasos 2 y 4.
Limpiar scripts. No lleve esto a cabo si quiere conservar las vistas y los procedimientos almacenados creados en los pasos 2 y 4.

SELECT: cláusula FOR (Transact-SQL)
OPENJSON (Transact-SQL)
JSON Functions (Transact-SQL) (Funciones JSON [Transact-SQL])
ISJSON (Transact-SQL)
JSON_VALUE (Transact-SQL)
JSON_OBJECT (Transact-SQL)
JSON_ARRAY (Transact-SQL)
JSON_QUERY (Transact-SQL)
JSON_MODIFY (Transact-SQL)

Compartir vía

Datos JSON en SQL Server

Funcionalidades clave de JSON de SQL Server y SQL Database

Tipo de datos JSON

Extraer valores de texto JSON y usarlos en consultas

Cambiar valores de JSON

Convertir colecciones de JSON en un conjunto de filas

Convertir datos de SQL Server a JSON o exportar JSON

Datos JSON de agregados

Casos de uso para datos JSON en SQL Server

Simplificación de modelos de datos complejos

Almacenamiento de datos de comercio electrónico y de venta al por menor

Procesamiento de datos de telemetría y registros

Almacenamiento de datos de IoT semiestructurados

Simplificación del desarrollo de la API de REST

Combinar datos relacionales y datos JSON

Almacenamiento e índice de datos JSON en SQL Server

Cargar archivos JSON en SQL Server

Importar datos JSON a tablas de SQL Server

Analizar datos JSON con consultas SQL

Obtener datos de una tabla de SQL Server con formato JSON

Probar la compatibilidad integrada de JSON con la base de datos de ejemplo de AdventureWorks

Comentarios

Recursos adicionales

Compartir vía

Datos JSON en SQL Server

Funcionalidades clave de JSON de SQL Server y SQL Database

Tipo de datos JSON

Extraer valores de texto JSON y usarlos en consultas

Cambiar valores de JSON

Convertir colecciones de JSON en un conjunto de filas

Convertir datos de SQL Server a JSON o exportar JSON

Datos JSON de agregados

Casos de uso para datos JSON en SQL Server

Simplificación de modelos de datos complejos

Almacenamiento de datos de comercio electrónico y de venta al por menor

Procesamiento de datos de telemetría y registros

Almacenamiento de datos de IoT semiestructurados

Simplificación del desarrollo de la API de REST

Combinar datos relacionales y datos JSON

Almacenamiento e índice de datos JSON en SQL Server

Cargar archivos JSON en SQL Server

Importar datos JSON a tablas de SQL Server

Analizar datos JSON con consultas SQL

Obtener datos de una tabla de SQL Server con formato JSON

Probar la compatibilidad integrada de JSON con la base de datos de ejemplo de AdventureWorks

Contenido relacionado

Comentarios

Recursos adicionales