Datos JSON en SQL ServerJSON data in SQL Server

SE APLICA A: síSQL Server síAzure SQL Database síAzure Synapse Analytics (SQL DW) noAlmacenamiento de datos paralelos APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

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 is a popular textual data format that's used for exchanging data in modern web and mobile applications. 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.JSON is also used for storing unstructured data in log files or NoSQL databases such as Microsoft Azure Cosmos DB. Muchos servicios web REST devuelven resultados con formato de texto JSON o bien aceptan datos con este formato.Many REST web services return results that are formatted as JSON text or accept data that's formatted as JSON. 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.For example, most Azure services, such as Azure Search, Azure Storage, and Azure Cosmos DB, have REST endpoints that return or consume JSON. JSON es también el formato principal para intercambiar datos entre páginas web y servidores web a través de llamadas AJAX.JSON is also the main format for exchanging data between webpages and web servers by using AJAX calls.

Las funciones JSON de SQL Server permiten combinar conceptos NoSQL y relacionales en la misma base de datos.JSON functions in SQL Server enable you to combine NoSQL and relational concepts in the same database. Ahora puede 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.Now you can combine classic relational columns with columns that contain documents formatted as JSON text in the same table, parse and import JSON documents in relational structures, or format relational data to JSON text. Vea cómo las funciones JSON conectan conceptos NoSQL y relacionales en SQL Server y Azure SQL Database en el vídeo siguiente:You see how JSON functions connect relational and NoSQL concepts in SQL Server and Azure SQL Database in the following video:

JSON as a bridge between NoSQL and relational worlds (JSON como puente entre los universos NoSQL y relacional)JSON as a bridge between NoSQL and relational worlds

Este es un ejemplo de texto JSON:Here's an example of JSON text:

[
  {
    "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:By using SQL Server built-in functions and operators, you can do the following things with JSON text:

  • Analizar texto JSON y leer o modificar valores.Parse JSON text and read or modify values.
  • Transformar matrices de objetos JSON a formato de tabla.Transform arrays of JSON objects into table format.
  • Ejecutar cualquier consulta de Transact-SQL en los objetos JSON convertidos.Run any Transact-SQL query on the converted JSON objects.
  • Dar formato JSON a los resultados de consultas de Transact-SQL.Format the results of Transact-SQL queries in JSON format.

Información general de la compatibilidad integrada de JSONOverview of built-in JSON support

Funcionalidades clave de JSON de SQL Server y SQL DatabaseKey JSON capabilities of SQL Server and SQL Database

En las siguientes secciones se analizan las funcionalidades clave que proporciona SQL Server con su compatibilidad de JSON integrada.The next sections discuss the key capabilities that SQL Server provides with its built-in JSON support. Puede ver cómo se usan los operadores y las funciones JSON en el vídeo siguiente:You can see how to use JSON functions and operators in the following video:

SQL Server 2016 and JSON Support (SQL Server 2016 y compatibilidad con JSON)SQL Server 2016 and JSON Support

Extraer valores de texto JSON y usarlos en consultasExtract values from JSON text and use them in queries

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:If you have JSON text that's stored in database tables, you can read or modify values in the JSON text by using the following built-in functions:

EjemploExample

En el siguiente ejemplo, la consulta usa datos tanto relacionales como JSON (almacenados en la columna denominada jsonCol) de una tabla:In the following example, the query uses both relational and JSON data (stored in a column named jsonCol) from a table:

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.Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. 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.).You can use values from JSON text in any part of a Transact-SQL query (including WHERE, ORDER BY, or GROUP BY clauses, window aggregates, and so on). Las funciones JSON usan una sintaxis parecida a la de JavaScript para hacer referencia a los valores de texto JSON.JSON functions use JavaScript-like syntax for referencing values inside JSON text.

Para obtener más información, consulte Validar, consultar y cambiar datos JSON con funciones integradas (SQL Server), JSON_VALUE (Transact-SQL) y JSON_QUERY (Transact-SQL).For more information, see Validate, query, and change JSON data with built-in functions (SQL Server), JSON_VALUE (Transact-SQL), and JSON_QUERY (Transact-SQL).

Cambiar valores de JSONChange JSON values

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.If you must modify parts of JSON text, you can use the JSON_MODIFY (Transact-SQL) function to update the value of a property in a JSON string and return the updated JSON string. En el siguiente ejemplo se actualiza el valor de una propiedad de una variable que contiene JSON:The following example updates the value of a property in a variable that contains 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;

ResultadoResults

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

Convertir colecciones de JSON en un conjunto de filasConvert JSON collections to a rowset

No es necesario un lenguaje de consulta personalizado para consultar JSON en SQL Server.You don't need a custom query language to query JSON in SQL Server. Para consultar datos JSON, puede usar T-SQL estándar.To query JSON data, you can use standard T-SQL. 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.If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. Para obtener más información, consulte Análisis y transformación de datos JSON con OPENJSON (SQL Server).For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

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:The following example calls OPENJSON and transforms the array of objects that is stored in the @json variable to a rowset that can be queried with a standard SQL SELECT statement:

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'
  );

ResultadoResults

Id.ID firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth
22 JohnJohn SmithSmith 2525
55 JaneJane SmithSmith 2005-11-04T12:00:002005-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.OPENJSON transforms the array of JSON objects into a table in which each object is represented as one row, and key/value pairs are returned as cells. El resultado detecta las siguientes reglas:The output observes the following rules:

  • OPENJSON convierte valores JSON a los tipos especificados en la cláusula WITH.OPENJSON converts JSON values to the types that are specified in the WITH clause.
  • OPENJSON se puede encargar tanto de los pares clave-valor sin formato como de los objetos anidados organizados jerárquicamente.OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • No hay que devolver todos los campos incluidos en el texto JSON.You don't have to return all the fields that are contained in the JSON text.
  • Si no existen valores JSON, OPENJSON devuelve valores NULL.If JSON values don't exist, OPENJSON returns NULL values.
  • 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.You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • El prefijo opcional strict en la ruta de acceso señala que los valores de las propiedades especificadas deben existir en el texto JSON.The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

Para obtener más información, consulte Análisis y transformación de datos JSON con OPENJSON (SQL Server) y OPENJSON (Transact-SQL).For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

Los documentos JSON pueden tener subelementos y datos jerárquicos que no se pueden asignar directamente a las columnas relacionales estándares.JSON documents may have sub-elements and hierarchical data that cannot be directly mapped into the standard relational columns. En este caso, es posible simplificar la jerarquía JSON mediante la combinación de la entidad primaria con submatrices.In this case, you can flatten JSON hierarchy by joining parent entity with sub-arrays.

En el ejemplo siguiente, el segundo objeto de la matriz tiene una submatriz que representa las aptitudes de una persona.In the following example, the second object in the array has sub-array representing person skills. Todos los objetos secundarios se pueden analizar mediante una llamada adicional a la función OPENJSON:Every sub-object can be parsed using additional OPENJSON function call:

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 *  
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 aptitudes 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.skills array is returned in the first OPENJSON as original JSON text fragment and passed to another OPENJSON function using APPLY operator. 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.The second OPENJSON function will parse JSON array and return string values as single column rowset that will be joined with the result of the first OPENJSON. El resultado de esta consulta se muestra en la tabla siguiente:The result of this query is shown in the following table:

ResultadoResults

Id.ID firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth aptitudesskill
22 JohnJohn SmithSmith 2525
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 SQLSQL
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 C#C#
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00 AzureAzure

OUTER APPLY OPENJSON se unirá a la entidad de primer nivel con la submatriz y devolverá un conjunto de resultados sin formato.OUTER APPLY OPENJSON will join first level entity with sub-array and return flatten resultset. Debido a JOIN, la segunda fila se repetirá para cada aptitud.Due to JOIN, the second row will be repeated for every skill.

Convertir datos de SQL Server a JSON o exportar JSONConvert SQL Server data to JSON or export JSON

Nota

No se admite la conversión de datos de Azure SQL Data Warehouse a JSON ni la exportación de JSON.Converting Azure SQL Data Warehouse data to JSON or exporting JSON is not supported.

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 .Format SQL Server data or the results of SQL queries as JSON by adding the FOR JSON clause to a SELECT statement. Use FOR JSON para delegar en SQL Server la aplicación de formato de los resultados de JSON de las aplicaciones cliente.Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. Para obtener más información, consulte Dar formato JSON a los resultados de consulta con FOR JSON (SQL Server).For more information, see Format Query Results as JSON with FOR JSON (SQL Server).

En el siguiente ejemplo se usa el modo PATH con la cláusula FOR JSON:The following example uses PATH mode with the FOR JSON clause:

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

El FOR JSON cambia el formato de los resultados SQL a texto JSON que podrá usarse en cualquier aplicación que comprenda JSON.The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands 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.The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

ResultadoResults

[
  {
    "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, consulte Dar formato JSON a los resultados de consulta con FOR JSON (SQL Server) y FOR Clause (Transact-SQL) (Cláusula FOR [Transact-SQL]).For more information, see Format query results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

Casos de uso para datos JSON en SQL ServerUse cases for JSON data in SQL Server

La compatibilidad de JSON con SQL Server y Azure SQL Database le permite combinar conceptos relacionales y de NoSQL.JSON support in SQL Server and Azure SQL Database lets you combine relational and NoSQL concepts. Puede transformar fácilmente los datos semiestructurados en relacionales y viceversa.You can easily transform relational to semi-structured data and vice-versa. Sin embargo, JSON no es un reemplazo para los modelos relacionales existentes.JSON is not a replacement for existing relational models, however. Estos son algunos casos de uso específicos que se benefician de la compatibilidad de JSON en SQL Server y SQL Database.Here are some specific use cases that benefit from the JSON support in SQL Server and in SQL Database.

Simplificación de modelos de datos complejosSimplify complex data models

Le recomendamos que desnormalice el modelo de datos con campos JSON en lugar de varias tablas secundarias.Consider denormalizing your data model with JSON fields in place of multiple child tables.

Almacenamiento de datos de comercio electrónico y de venta al por menorStore retail and e-commerce data

Almacene información sobre productos con una amplia gama de atributos variables en un modelo desnormalizado para obtener una mayor flexibilidad.Store info about products with a wide range of variable attributes in a denormalized model for flexibility.

Procesamiento de datos de telemetría y registrosProcess log and telemetry data

Cargue, consulte y analice datos de registro almacenados como archivos JSON con toda la potencia del lenguaje Transact-SQL.Load, query, and analyze log data stored as JSON files with all the power of the Transact-SQL language.

Almacenamiento de datos de IoT semiestructuradosStore semi-structured IoT data

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.When you need real-time analysis of IoT data, load the incoming data directly into the database instead of staging it in a storage location.

Simplificación del desarrollo de la API de RESTSimplify REST API development

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.Transform relational data from your database easily into the JSON format used by the REST APIs that support your web site.

Combinar datos relacionales y datos JSONCombine relational and JSON data

SQL Server proporciona un modelo híbrido para almacenar y procesar datos JSON y relacionales usando el lenguaje Transact-SQL estándar.SQL Server provides a hybrid model for storing and processing both relational and JSON data by using standard Transact-SQL language. 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.You can organize collections of your JSON documents in tables, establish relationships between them, combine strongly typed scalar columns stored in tables with flexible key/value pairs stored in JSON columns, and query both scalar and JSON values in one or more tables by using full Transact-SQL.

El texto JSON se almacena en columnas VARCHAR o NVARCHAR y se indexa como texto sin formato.JSON text is stored in VARCHAR or NVARCHAR columns and is indexed as plain text. 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.Any SQL Server feature or component that supports text supports JSON, so there are almost no constraints on interaction between JSON and other SQL Server features. Así, JSON puede almacenar en tablas temporales o tablas en memoria, aplicar predicados de seguridad de nivel de fila al texto JSON, etc.You can store JSON in In-memory or Temporal tables, apply Row-Level Security predicates on JSON text, and so on.

Si tiene cargas de trabajo JSON puras en las que quiera usar algún tipo de lenguaje de consulta personalizado para procesar documentos JSON, sopese la posibilidad de usar Microsoft Azure Cosmos DB.If you have pure JSON workloads where you want to use a query language that's customized for the processing of JSON documents, consider Microsoft Azure Cosmos DB.

Estos son algunos casos de uso que muestran cómo se puede usar la compatibilidad integrada de JSON en SQL ServerSQL Server.Here are some use cases that show how you can use the built-in JSON support in SQL ServerSQL Server.

Almacenamiento e índice de datos JSON en SQL ServerStore and index JSON data in 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.JSON is a textual format so the JSON documents can be stored in NVARCHAR columns in a SQL Database. Como el tipo NVARCHAR es compatible con todos los subsistemas de SQL Server, puede colocar los documentos JSON en tablas con índices CLUSTERED COLUMNSTORE, tablas optimizadas para memoria o archivos externos que se pueden leer mediante OPENROWSET o PolyBase.Since NVARCHAR type is supported in all SQL Server sub-systems you can put JSON documents in tables with CLUSTERED COLUMNSTORE indexes, memory optimized tables, or external files that can be read using OPENROWSET or 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:To learn more about your options for storing, indexing, and optimizing JSON data in SQL Server, see the following articles:

Cargar archivos JSON en SQL ServerLoad JSON files into SQL Server

Puede dar formato a información que se almacena en archivos JSON estándar o JSON delimitado por línea.You can format information that's stored in files as standard JSON or line-delimited JSON. SQL Server puede importar el contenido de archivos JSON, analizarlo mediante las funciones OPENJSON o JSON_VALUE, y cargarlo en tablas.SQL Server can import the contents of JSON files, parse it by using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • 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.If your JSON documents are stored in local files, on shared network drives, or in Azure Files locations that can be accessed by SQL Server, you can use bulk import to load your JSON data into 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.If your line-delimited JSON files are stored in Azure Blob storage or the Hadoop file system, you can use PolyBase to load JSON text, parse it in Transact-SQL code, and load it into tables.

Importar datos JSON a tablas de SQL ServerImport JSON data into SQL Server tables

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.If you must load JSON data from an external service into SQL Server, you can use OPENJSON to import the data into SQL Server instead of parsing the data in the application layer.

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.You can provide the content of the JSON variable by an external REST service, send it as a parameter from a client-side JavaScript framework, or load it from external files. Los resultados de texto JSON se pueden insertar, actualizar o combinar fácilmente en tablas de SQL Server.You can easily insert, update, or merge results from JSON text into a SQL Server table.

Analizar datos JSON con consultas SQLAnalyze JSON data with SQL queries

Si tiene que filtrar o agregar datos JSON para elaborar informes, puede usar OPENJSON para transformar JSON en formato relacional.If you must filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. Luego puede usar el lenguaje Transact-SQLTransact-SQL estándar y las funciones integradas para preparar los informes.You can then use standard Transact-SQLTransact-SQL and built-in functions to prepare the reports.

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.You can use both standard table columns and values from JSON text in the same query. Puede agregar índices a la expresión JSON_VALUE(Tab.json, '$.Status') para mejorar el rendimiento de la consulta.You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve the performance of the query. Para obtener más información, consulte Indexación de datos JSON.For more information, see Index JSON data.

Obtener datos de una tabla de SQL Server con formato JSONReturn data from a SQL Server table formatted as 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.If you have a web service that takes data from the database layer and returns it in JSON format, or if you have JavaScript frameworks or libraries that accept data formatted as JSON, you can format JSON output directly in a SQL query. 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.Instead of writing code or including a library to convert tabular query results and then serialize objects to JSON format, you can use FOR JSON to delegate the JSON formatting to SQL Server.

Por ejemplo, imaginemos que quiere generar un resultado JSON que sea compatible con la especificación OData.For example, you might want to generate JSON output that's compliant with the OData specification. El servicio web espera una solicitud y una respuesta en el siguiente formato:The web service expects a request and response in the following format:

  • Solicitud: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductNameRequest: /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"}Response: {"@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.This OData URL represents a request for the ProductID and ProductName columns for the product with ID 1. Puede usar FOR JSON para aplicar al resultado el formato que SQL Server espera.You can use FOR JSON to format the output as expected in SQL Server.

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;

El resultado de esta consulta es un texto JSON totalmente compatible con la especificación OData. SQL Server se encarga de aplicar el formato y las secuencias de escape.The output of this query is JSON text that's fully compliant with the OData spec. Formatting and escaping are handled by SQL Server. SQL Server también puede dar formato a resultados de consulta en cualquier formato, como JSON de OData o GeoJSON.SQL Server can also format query results in any format, such as OData JSON or GeoJSON.

Probar la compatibilidad integrada de JSON con la base de datos de ejemplo de AdventureWorksTest drive built-in JSON support with the AdventureWorks sample database

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 desde el Centro de descarga de Microsoft.To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from Microsoft Download Center.

Después de restaurar la base de datos de ejemplo en una instancia de SQL Server 2016, extraiga el archivo de ejemplos y abra el archivo JSON Sample Queries procedures views and indexes.sql desde la carpeta JSON.After you restore the sample database to an instance of SQL Server 2016, extract the samples file, and then open the JSON Sample Queries procedures views and indexes.sql file from the JSON folder. 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.Run the scripts in this file to reformat some existing data as JSON data, test sample queries and reports over the JSON data, index the JSON data, and import and export JSON.

Esto es lo que se puede hacer con los scripts incluidos en el archivo:Here's what you can do with the scripts that are included in the file:

  • Desnormalizar el esquema existente para crear columnas de datos JSON.Denormalize the existing schema to create columns of JSON data.

    • Almacene la información SalesReasons, SalesOrderDetails, SalesPerson, Customer y otras tablas con información relativa a pedidos de ventas en columnas JSON de la tabla SalesOrder_json.Store information from SalesReasons, SalesOrderDetails, SalesPerson, Customer, and other tables that contain information related to sales order into JSON columns in the SalesOrder_json table.

    • Almacene información de las tablas EmailAddresses/PersonPhone en la tabla Person_json como matrices de objetos JSON.Store information from EmailAddresses/PersonPhone tables in the Person_json table as arrays of JSON objects.

  • Crear procedimientos y vistas que consultan datos JSON.Create procedures and views that query JSON data.

  • Indexar datos JSON.Index JSON data. Cree índices en propiedades JSON e índices de texto completo.Create indexes on JSON properties and full-text indexes.

  • Importar y exportar JSON.Import and export JSON. Cree y ejecute procedimientos que exporten el contenido de las tablas Person y SalesOrder como resultados JSON. Importe y actualice las tablas Person y SalesOrder con entradas JSON.Create and run procedures that export the content of the Person and the SalesOrder tables as JSON results, and import and update the Person and the SalesOrder tables by using JSON input.

  • Ejecutar ejemplos de consultas.Run query examples. Ejecute algunas consultas que llamen a las vistas y los procedimientos almacenados creados en los pasos 2 y 4.Run some queries that call the stored procedures and views that you created in steps 2 and 4.

  • Limpiar scripts.Clean up scripts. No lleve esto a cabo si quiere conservar las vistas y los procedimientos almacenados creados en los pasos 2 y 4.Don't run this part if you want to keep the stored procedures and views that you created in steps 2 and 4.

Más información sobre JSON en SQL Server y Azure SQL DatabaseLearn more about JSON in SQL Server and Azure SQL Database

Vídeos de MicrosoftMicrosoft videos

Para obtener una introducción visual a la compatibilidad integrada de JSON en SQL Server y Azure SQL Database, vea el siguiente vídeo:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following video:

Using JSON in SQL Server 2016 and Azure SQL Database (Uso de JSON en SQL Server 2016 y Azure SQL Database)Using JSON in SQL Server 2016 and Azure SQL Database

Compilar una API de REST con SQL Server mediante funciones JSONBuilding REST API with SQL Server using JSON functions

Artículos de referenciaReference articles