Dados JSON no SQL ServerJSON data in SQL Server

APLICA-SE A: simSQL Server simBanco de Dados SQL do Azure simSQL Data Warehouse do Azure nãoParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse noParallel Data Warehouse

JSON é um formato popular de dados textuais usado para troca de dados em aplicativos Web e móveis modernos.JSON is a popular textual data format that's used for exchanging data in modern web and mobile applications. JSON também é usado para armazenar dados não estruturados em arquivos de log ou em bancos de dados NoSQL, como o Microsoft Azure Cosmos DB.JSON is also used for storing unstructured data in log files or NoSQL databases such as Microsoft Azure Cosmos DB. Muitos serviços Web REST retornam resultados formatados como texto JSON ou aceitam dados formatados como JSON.Many REST web services return results that are formatted as JSON text or accept data that's formatted as JSON. Por exemplo, a maioria dos serviços do Azure, como o Azure Search, o Armazenamento do Azure e o Azure Cosmos DB, tem pontos de extremidade REST que retornam ou consomem 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 também é o principal formato para troca de dados entre páginas da Web e servidores Web usando chamadas AJAX.JSON is also the main format for exchanging data between webpages and web servers by using AJAX calls.

As funções JSON no SQL Server permitem combinar NoSQL e conceitos relacionais no mesmo banco de dados.JSON functions in SQL Server enable you to combine NoSQL and relational concepts in the same database. Agora você pode combinar colunas relacionais clássicas com colunas que contêm documentos formatados como texto JSON na mesma tabela, analisar e importar documentos JSON em estruturas relacionais ou formatar dados relacionais em texto JSON.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. No vídeo a seguir, você verá como as funções JSON conectam conceitos relacionais e NoSQL no SQL Server e no Banco de Dados SQL do Azure:You see how JSON functions connect relational and NoSQL concepts in SQL Server and Azure SQL Database in the following video:

JSON é uma ponte entre o NoSQL e mundos relacionaisJSON as a bridge between NoSQL and relational worlds

Este é um texto JSON de exemplo:Here's an example of JSON text:

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

Usando funções internas e operadores do SQL Server, é possível realizar as ações a seguir com o texto JSON:By using SQL Server built-in functions and operators, you can do the following things with JSON text:

  • Analise texto JSON e leia ou modifique os valores.Parse JSON text and read or modify values.
  • Transforme matrizes de objetos JSON em formato de tabela.Transform arrays of JSON objects into table format.
  • Executar uma consulta Transact-SQL nos objetos JSON convertidos.Run any Transact-SQL query on the converted JSON objects.
  • Formate os resultados de consultas Transact-SQL em formato JSON.Format the results of Transact-SQL queries in JSON format.

Visão geral do suporte interno a JSONOverview of built-in JSON support

Principais funcionalidades do JSON no SQL Server e no Banco de dados SQLKey JSON capabilities of SQL Server and SQL Database

As seções a seguir discutem as principais funcionalidades fornecidas pelo SQL Server com seu suporte interno ao JSON.The next sections discuss the key capabilities that SQL Server provides with its built-in JSON support. No vídeo a seguir, você verá como usar os operadores e funções JSON:You can see how to use JSON functions and operators in the following video:

SQL Server 2016 e suporte para JSONSQL Server 2016 and JSON Support

Extrair valores do texto JSON e usá-los em consultasExtract values from JSON text and use them in queries

Caso haja texto JSON armazenado em tabelas de banco de dados, é possível ler ou modificar valores no texto JSON usando as seguintes funções internas: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:

ExemploExample

No exemplo a seguir, a consulta usa tanto dados JSON quanto relacionais (armazenados em uma coluna chamada jsonCol) de uma tabela: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')

Os aplicativos e ferramentas não percebem nenhuma diferença entre os valores extraídos de colunas de tabela escalar e os valores extraídos da coluna JSON.Applications and tools see no difference between the values taken from scalar table columns and the values taken from JSON columns. Você pode usar valores do texto JSON em qualquer parte de uma consulta Transact-SQL (incluindo, cláusulas WHERE, ORDER BY ou GROUP BY, agregações de janela e assim por diante).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). As funções JSON usam sintaxe semelhante a do JavaScript para fazer referência a valores no texto JSON.JSON functions use JavaScript-like syntax for referencing values inside JSON text.

Para obter mais informações, consulte Validar, consultar e alterar dados JSON com funções internas (SQL Server), JSON_VALUE (Transact-SQL)e 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).

Alterar os valores JSONChange JSON values

Se precisar modificar partes do texto JSON, use a função JSON_MODIFY (Transact-SQL) para atualizar o valor de uma propriedade em uma cadeia de caracteres JSON e retornar a cadeia de caracteres JSON atualizada.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. O exemplo a seguir atualiza o valor de uma propriedade em uma variável que contém 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;

ResultadosResults

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

Converter coleções JSON em um conjunto de linhasConvert JSON collections to a rowset

Você não precisa de uma linguagem de consulta personalizada para consultar o JSON no SQL Server.You don't need a custom query language to query JSON in SQL Server. Para consultar dados JSON, é possível usar o T-SQL padrão.To query JSON data, you can use standard T-SQL. Se precisar criar uma consulta ou um relatório sobre dados JSON, você poderá converter facilmente os dados JSON em linhas e colunas chamando a função de conjunto de linhas 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 obter mais informações, consulte Converter dados JSON em linhas e colunas com OPENJSON (SQL Server).For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).

O seguinte exemplo chama OPENJSON e transforma a matriz de objetos armazenados na variável @json em um conjunto de linhas que pode ser consultado com uma instrução SQL SELECT padrão: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')  

ResultadosResults

IDID firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth
22 JohnJohn SmithSmith 2525
55 JaneJane SmithSmith 2005-11-04T12:00:002005-11-04T12:00:00

OPENJSON transforma a matriz de objetos JSON em uma tabela, na qual cada objeto é representado como uma linha e os pares chave/valor são retornados como células.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. O resultado segue as seguintes regras:The output observes the following rules:

  • OPENJSON converte valores JSON nos tipos especificados na cláusula WITH.OPENJSON converts JSON values to the types that are specified in the WITH clause.
  • OPENJSON pode lidar com pares simples de chave:valor e objetos aninhados hierarquicamente organizados.OPENJSON can handle both flat key/value pairs and nested, hierarchically organized objects.
  • Você não precisa retornar todos os campos contidos no texto JSON.You don't have to return all the fields that are contained in the JSON text.
  • Se não houver valores JSON, o OPENJSON retornará valores NULL.If JSON values don't exist, OPENJSON returns NULL values.
  • Opcionalmente, é possível especificar um caminho após a especificação de tipo para referenciar uma propriedade aninhada ou uma propriedade por outro nome.You can optionally specify a path after the type specification to reference a nested property or to reference a property by a different name.
  • O prefixo strict opcional no caminho especifica que os valores para as propriedades especificadas devem existir no texto JSON.The optional strict prefix in the path specifies that values for the specified properties must exist in the JSON text.

Para obter mais informações, consulte Converter dados JSON em linhas e colunas com OPENJSON (SQL Server) e OPENJSON (Transact-SQL).For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server) and OPENJSON (Transact-SQL).

Os documentos JSON podem ter subelementos e dados hierárquicos que não podem ser mapeados diretamente nas colunas relacionais padrão.JSON documents may have sub-elements and hierarchical data that cannot be directly mapped into the standard relational columns. Nesse caso, você poderá mesclar a hierarquia JSON unindo a entidade pai às submatrizes.In this case, you can flatten JSON hierarchy by joining parent entity with sub-arrays.

No exemplo a seguir, o segundo objeto na matriz tem uma submatriz que representa as habilidades da pessoa.In the following example, the second object in the array has sub-array representing person skills. Cada subobjeto pode ser analisado usando uma chamada adicional à função 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) '$' )

A matriz skills é retornada no primeiro OPENJSON como um fragmento de texto JSON original e passada para outra função OPENJSON usando o operador APPLY.skills array is returned in the first OPENJSON as original JSON text fragment and passed to another OPENJSON function using APPLY operator. A segunda função OPENJSON analisará a matriz JSON e retornará valores de cadeia de caracteres como um único conjunto de linhas de coluna que será associado ao resultado do primeiro 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. O resultado dessa consulta é mostrado na seguinte tabela:The result of this query is shown in the following table:

ResultadosResults

IDID firstNamefirstName lastNamelastName ageage dateOfBirthdateOfBirth skillskill
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 unirá a entidade de primeiro nível à submatriz e retornará um conjunto de resultados mesclado.OUTER APPLY OPENJSON will join first level entity with sub-array and return flatten resultset. Devido ao uso de JOIN, a segunda linha será repetida para cada habilidade.Due to JOIN, the second row will be repeated for every skill.

Converter dados do SQL Server em JSON ou exportar JSONConvert SQL Server data to JSON or export JSON

Observação

Não há suporte para exportar JSON ou converter dados do SQL Data Warehouse do Azure para JSON.Converting Azure SQL Data Warehouse data to JSON or exporting JSON is not supported.

Formate dados do SQL Server ou os resultados de consultas SQL como JSON, adicionando a cláusula FOR JSON a uma instrução 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 a formatação da saída JSON do seu aplicativo cliente ao SQL Server.Use FOR JSON to delegate the formatting of JSON output from your client applications to SQL Server. Para obter mais informações, consulte Formatar Resultados da Pesquisa como JSON para FOR JSON (SQL Server).For more information, see Format Query Results as JSON with FOR JSON (SQL Server).

O exemplo a seguir usa o modo PATH com a 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  

O FOR JSON formata os resultados do SQL como texto JSON, que pode ser fornecido a qualquer aplicativo que entenda JSON.The FOR JSON clause formats SQL results as JSON text that can be provided to any app that understands JSON. A opção PATH usa aliases separados por ponto na cláusula SELECT para aninhar objetos nos resultados da consulta.The PATH option uses dot-separated aliases in the SELECT clause to nest objects in the query results.

ResultadosResults

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

Para obter mais informações, consulte Formatar Resultados da Pesquisa como JSON para FOR JSON (SQL Server) e Cláusula FOR (Transact-SQL).For more information, see Format query results as JSON with FOR JSON (SQL Server) and FOR Clause (Transact-SQL).

Usar casos para dados JSON no SQL ServerUse cases for JSON data in SQL Server

O suporte a JSON no SQL Server e no Banco de Dados SQL do Azure permite combinar conceitos relacionais e NoSQL.JSON support in SQL Server and Azure SQL Database lets you combine relational and NoSQL concepts. É possível transformar facilmente dados relacionais em semiestruturados e vice-versa.You can easily transform relational to semi-structured data and vice-versa. No entanto, o JSON não é uma substituição para modelos relacionais existentes.JSON is not a replacement for existing relational models, however. Aqui estão alguns casos de uso específicos que se beneficiam do suporte a JSON no SQL Server e no Banco de Dados SQL.Here are some specific use cases that benefit from the JSON support in SQL Server and in SQL Database.

Simplificar modelos de dados complexosSimplify complex data models

Considere a desnormalização de seu modelo de dados com campos JSON em vez de várias tabelas filho.Consider denormalizing your data model with JSON fields in place of multiple child tables.

Armazenar dados de varejo e de comércio eletrônicoStore retail and e-commerce data

Armazene informações sobre produtos com uma ampla gama de atributos de variável em um modelo desnormalizado para obter flexibilidade.Store info about products with a wide range of variable attributes in a denormalized model for flexibility.

Processar dados de log e de telemetriaProcess log and telemetry data

Carregue, consulte e analise dados de log armazenados como arquivos JSON com todos os recursos da linguagem Transact-SQL.Load, query, and analyze log data stored as JSON files with all the power of the Transact-SQL language.

Armazenar dados semiestruturados de IoTStore semi-structured IoT data

Quando precisar de análise em tempo real de dados de IoT, carregue os dados de entrada diretamente no banco de dados, em vez de prepará-los em um local de armazenamento.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.

Simplificar o desenvolvimento da API RESTSimplify REST API development

Transforme dados relacionais do seu banco de dados facilmente no formato JSON usado pelas APIs REST compatíveis com seu site da Web.Transform relational data from your database easily into the JSON format used by the REST APIs that support your web site.

Combinar dados relacionais e dados JSONCombine relational and JSON data

O SQL Server fornece um modelo híbrido para armazenar e processar dados relacionais e JSON usando linguagem padrão Transact-SQL.SQL Server provides a hybrid model for storing and processing both relational and JSON data by using standard Transact-SQL language. Você pode organizar coleções de seus documentos JSON em tabelas, estabelecer relações entre elas, combinar colunas escalares fortemente tipadas armazenadas em tabelas com pares chave/valor flexíveis armazenadas em colunas JSON e consultar valores escalares e JSON em uma ou várias tabelas usando o Transact-SQL 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.

O texto JSON é armazenado em colunas varchar ou nvarchar e é indexado como texto sem formatação.JSON text is stored in varchar or nvarchar columns and is indexed as plain text. Qualquer componente ou recurso do SQL Server que dá suporte a texto dá suporte a JSON e, portanto, quase não há restrições na interação entre JSON e outros recursos do 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. Você pode armazenar o JSON na Memória ou em Tabelas temporais, aplicar predicados de Segurança em nível de linha em texto JSON e assim por diante.You can store JSON in In-memory or Temporal tables, apply Row-Level Security predicates on JSON text, and so on.

Se você tiver cargas de trabalhos JSON puras nas quais queira usar uma linguagem de consulta que seja personalizada para o processamento de documentos JSON, considere o 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.

Veja alguns casos de uso que mostram como é possível usar o suporte JSON interno no SQL ServerSQL Server.Here are some use cases that show how you can use the built-in JSON support in SQL ServerSQL Server.

Armazenar e indexar dados JSON no SQL ServerStore and index JSON data in SQL Server

JSON é um formato textual para que documentos JSON possam ser armazenados em colunas NVARCHAR no Banco de Dados SQL.JSON is a textual format so the JSON documents can be stored in NVARCHAR columns in a SQL Database. Como o tipo NVARCHAR dá suporte a todos os subsistemas do SQL Server, é possível colocar documentos JSON em tabelas com índices CLUSTERED COLUMNSTORE, tabelas otimizadas para memória ou arquivos externos que podem ser lidos usando OPENROWSET ou 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 saber mais sobre as opções de armazenamento, indexação e otimização dos dados JSON no SQL Server, consulte os seguintes artigos:To learn more about your options for storing, indexing, and optimizing JSON data in SQL Server, see the following articles:

Carregar arquivos JSON no SQL ServerLoad JSON files into SQL Server

É possível formatar informações armazenadas em arquivos como JSON padrão ou JSON delimitado por linha.You can format information that's stored in files as standard JSON or line-delimited JSON. O SQL Server pode importar o conteúdo de arquivos JSON, analisá-lo usando as funções OPENJSON ou JSON_VALUE e carregá-lo em tabelas.SQL Server can import the contents of JSON files, parse it by using the OPENJSON or JSON_VALUE functions, and load it into tables.

  • Se seus documentos JSON estiverem armazenados em arquivos locais, em unidades de rede compartilhadas ou em locais de Arquivos do Azure que podem ser acessados pelo SQL Server, você poderá usar a importação em massa para carregar os dados JSON no 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.

  • Se os arquivos delimitados por linha estiverem armazenados no sistema de arquivos do Hadoop ou no Armazenamento de Blobs do Azure, você poderá usar o PolyBase para carregar texto JSON, analisá-lo no código Transact-SQL e carregá-lo em tabelas.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 dados JSON em tabelas do SQL ServerImport JSON data into SQL Server tables

Se precisar carregar dados JSON de um serviço externo no SQL Server, você poderá usar OPENJSON para importar os dados para o SQL Server em vez de analisá-los na camada de aplicativo.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 SalesReport  
SELECT SalesOrderJsonData.*  
FROM OPENJSON (@jsonVariable, 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;  

Você pode fornecer o conteúdo da variável JSON por um serviço REST externo, enviado como um parâmetro de uma estrutura JavaScript do lado do cliente ou carregado de arquivos 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. Você pode inserir, atualizar ou mesclar facilmente resultados de texto JSON em uma tabela do SQL Server.You can easily insert, update, or merge results from JSON text into a SQL Server table.

Analisar dados JSON com consultas SQLAnalyze JSON data with SQL queries

Se precisar filtrar ou agregar dados JSON para fins de relatório, você poderá usar OPENJSON para transformar o JSON em um formato relacional.If you must filter or aggregate JSON data for reporting purposes, you can use OPENJSON to transform JSON to relational format. Você pode usar o Transact-SQLTransact-SQL padrão e funções internas para preparar os relatórios.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  

Você pode usar colunas de tabela padrão e valores do texto JSON na mesma consulta.You can use both standard table columns and values from JSON text in the same query. É possível adicionar índices na expressão JSON_VALUE(Tab.json, '$.Status') para melhorar o desempenho da consulta.You can add indexes on the JSON_VALUE(Tab.json, '$.Status') expression to improve the performance of the query. Para obter mais informações, consulte Indexar dados JSON.For more information, see Index JSON data.

Retornar dados de uma tabela do SQL Server formatada como JSONReturn data from a SQL Server table formatted as JSON

Se tiver um serviço Web que usa dados da camada do banco de dados e os retorna no formato JSON ou se tiver estruturas ou bibliotecas JavaScript que aceitam dados formatados como JSON, você poderá formatar o resultado em JSON diretamente em uma consulta 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. Em vez de escrever código ou incluir uma biblioteca para converter resultados da consulta de tabela e serializar objetos no formato JSON, você pode usar FOR JSON para delegar a formatação JSON ao SQL Server.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 exemplo, pode ser conveniente gerar uma saída JSON em conformidade com a especificação OData.For example, you might want to generate JSON output that's compliant with the OData specification. O serviço Web espera uma solicitação e uma resposta no formato a seguir:The web service expects a request and response in the following format:

  • Solicitação: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductNameRequest: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

  • Resposta: {"@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"}

Essa URL do OData representa uma solicitação para as colunas ProductID e ProductName do produto com ID 1.This OData URL represents a request for the ProductID and ProductName columns for the product with ID 1. Você pode usar FOR JSON para formatar o resultado conforme esperado no SQL Server.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  

A saída dessa consulta é um texto JSON que está em conformidade total com a especificação OData. A formatação e a saída são manipuladas pelo SQL Server.The output of this query is JSON text that's fully compliant with the OData spec. Formatting and escaping are handled by SQL Server. O SQL Server também pode formatar resultados de consulta em qualquer formato, como OData JSON ou GeoJSON.SQL Server can also format query results in any format, such as OData JSON or GeoJSON.

Faça o test drive do suporte interno para JSON com o banco de dados de exemplo AdventureWorksTest drive built-in JSON support with the AdventureWorks sample database

Para obter o banco de dados de exemplo AdventureWorks, baixe pelo menos o arquivo de banco de dados e o arquivo de exemplos e scripts no Centro de Download da Microsoft.To get the AdventureWorks sample database, download at least the database file and the samples and scripts file from Microsoft Download Center.

Após restaurar o banco de dados de exemplo para uma instância do SQL Server 2016, extraia o arquivo de exemplos e abra o arquivo JSON Sample Queries procedures views and indexes.sql da pasta 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. Execute os scripts nesse arquivo para reformatar alguns dados existentes como dados JSON, testar relatórios e consultas de exemplo em dados JSON, indexar os dados JSON e importar e 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.

Veja o que você pode fazer com os scripts incluídos no arquivo:Here's what you can do with the scripts that are included in the file:

  • Desnormalize o esquema existente para criar colunas de dados JSON.Denormalize the existing schema to create columns of JSON data.

    • Armazene informações de SalesReasons, SalesOrderDetails, SalesPerson, Customer e outras tabelas que contenham informações relacionadas a ordens de venda nas colunas JSON da tabela 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.

    • Armazene informações das tabelas EmailAddresses/PersonPhone na tabela Person_json como matrizes de objetos JSON.Store information from EmailAddresses/PersonPhone tables in the Person_json table as arrays of JSON objects.

  • Crie procedimentos e exibições que consultem dados JSON.Create procedures and views that query JSON data.

  • Indexar dados JSON.Index JSON data. Crie índices em propriedades JSON e índices de texto completo.Create indexes on JSON properties and full-text indexes.

  • Importar e exportar JSON.Import and export JSON. Crie e execute procedimentos que exportem o conteúdo das tabelas Person e SalesOrder como resultados JSON, e importe e atualize as tabelas Person e SalesOrder usando a entrada 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.

  • Executar exemplos de consulta.Run query examples. Execute algumas consultas que chamem os procedimentos armazenados e exibições criados nas etapas 2 e 4.Run some queries that call the stored procedures and views that you created in steps 2 and 4.

  • Limpar scripts.Clean up scripts. Não execute essa parte se quiser manter os procedimentos armazenados e exibições criados nas etapas 2 e 4.Don't run this part if you want to keep the stored procedures and views that you created in steps 2 and 4.

Saiba mais sobre JSON no SQL Server e no Banco de Dados SQL do AzureLearn more about JSON in SQL Server and Azure SQL Database

Vídeos da MicrosoftMicrosoft videos

Para obter uma introdução visual do suporte interno a JSON no SQL Server e no Banco de Dados SQL do Azure, assista ao vídeo a seguir:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following video:

Usando JSON no SQL Server 2016 e no Banco de Dados SQL do AzureUsing JSON in SQL Server 2016 and Azure SQL Database

Building REST API with SQL Server using JSON functions (Criando a API REST com o SQL Server usando funções JSON)Building REST API with SQL Server using JSON functions

Artigos de referênciaReference articles