Formatar os resultados da consulta como JSON com o FOR JSON (SQL Server)Format Query Results as JSON with FOR JSON (SQL Server)

• 06/06/2019
• 11 minutos para ler
• • 
  • 
  • 
  • 

APLICA-SE A: SQL Server Banco de Dados SQL do Azure Azure Synapse Analytics (SQL DW) Parallel Data Warehouse APPLIES TO: SQL Server Azure SQL Database Azure Synapse Analytics (SQL DW) Parallel Data Warehouse

Formate os resultados da consulta ou exporte os dados do SQL Server como JSON adicionando a cláusula FOR JSON a uma instrução SELECT.Format query results as JSON, or export data from SQL Server as JSON, by adding the FOR JSON clause to a SELECT statement. Use a cláusula FOR JSON para simplificar os aplicativos cliente ao delegar a formatação da saída JSON do aplicativo para SQL ServerSQL Server.Use the FOR JSON clause to simplify client applications by delegating the formatting of JSON output from the app to SQL ServerSQL Server.

Ao usar a cláusula FOR JSON, é possível especificar explicitamente a estrutura da saída JSON ou permitir que a estrutura da instrução SELECT determine a saída.When you use the FOR JSON clause, you can specify the structure of the JSON output explicitly, or let the structure of the SELECT statement determine the output.

• Use FOR JSON PATH para manter controle total sobre o formato da saída JSON.To maintain full control over the format of the JSON output, use FOR JSON PATH. Você pode criar objetos wrapper e aninhar propriedades complexas.You can create wrapper objects and nest complex properties.

• Use FOR JSON AUTO para formatar a saída JSON automaticamente com base na estrutura da instrução SELECT.To format the JSON output automatically based on the structure of the SELECT statement, use FOR JSON AUTO.

Aqui está um exemplo de uma instrução SELECT com a cláusula FOR JSON e sua saída.Here's an example of a SELECT statement with the FOR JSON clause and its output.

Opção 1 – Controle de saída com FOR JSON PATHOption 1 - You control output with FOR JSON PATH

No modo PATH, você pode usar a sintaxe de ponto, por exemplo, 'Item.UnitPrice', para formatar a saída aninhada.In PATH mode, you can use the dot syntax - for example, 'Item.UnitPrice' - to format nested output.

Veja um exemplo de consulta que usa o modo PATH com a cláusula FOR JSON .Here's a sample query that uses PATH mode with the FOR JSON clause. O exemplo a seguir também usa a opção ROOT para especificar um elemento de raiz nomeado.The following example also uses the ROOT option to specify a named root element.

Mais informações sobre o FOR JSON PATHMore info about FOR JSON PATH

Para obter mais informações e exemplos, consulte Formato de saída JSON aninhado com modo PATH (SQL Server).For more detailed info and examples, see Format Nested JSON Output with PATH Mode (SQL Server).

Para sintaxe e uso, consulte Cláusula FOR (Transact-SQL).For syntax and usage, see FOR Clause (Transact-SQL).

Opção 2 – Saída dos controles de instrução SELECT com FOR JSON AUTOOption 2 - SELECT statement controls output with FOR JSON AUTO

No modo AUTO , a estrutura da instrução SELECT determina o formato da saída JSON.In AUTO mode, the structure of the SELECT statement determines the format of the JSON output.

Por padrão, os valores nulos não são incluídos na saída.By default, null values are not included in the output. Você pode usar o INCLUDE_NULL_VALUES para alterar esse comportamento.You can use the INCLUDE_NULL_VALUES to change this behavior.

Veja um exemplo de consulta que usa o modo AUTO com a cláusula FOR JSON .Here's a sample query that uses AUTO mode with the FOR JSON clause.

SELECT name, surname  
FROM emp  
FOR JSON AUTO;

E aqui está o JSON retornado.And here is the returned JSON.

[{
    "name": "John"
}, {
    "name": "Jane",
    "surname": "Doe"
}]

2.b – exemplo de JOIN e NULL2.b - Example with JOIN and NULL

O exemplo a seguir de SELECT...FOR JSON AUTO inclui uma exibição de como seria a aparência dos resultados JSON quando há um relacionamento de 1:Muitos entre os dados de tabelas com JOIN.The following example of SELECT...FOR JSON AUTO includes a display of what the JSON results look like when there is a 1:Many relationship between data from JOIN'ed tables.

A ausência do valor nulo no JSON retornado também é ilustrada.The absence of the null value from the returned JSON is also illustrated. No entanto, você pode substituir esse comportamento padrão usando a palavra-chave INCLUDE_NULL_VALUES na cláusula FOR.However, you can override this default behavior by use of the INCLUDE_NULL_VALUES keyword on the FOR clause.

go

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;

go

CREATE TABLE #tabClass
(
   ClassGuid   uniqueIdentifier  not null  default newid(),
   ClassName   nvarchar(32)      not null
);

CREATE TABLE #tabStudent
(
   StudentGuid   uniqueIdentifier  not null  default newid(),
   StudentName   nvarchar(32)      not null,
   ClassGuid     uniqueIdentifier      null   -- Foreign key.
);

go

INSERT INTO #tabClass
      (ClassGuid, ClassName)
   VALUES
      ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'),
      ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'),
      ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting')
;

INSERT INTO #tabStudent
      (StudentName, ClassGuid)
   VALUES
      ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'),
      ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
      ('Betty Boot' , 'C55C6819-E744-4797-AC56-FF8A729A7F5C'),
      ('Betty Boot' , '98509D36-A2C8-4A65-A310-E744F5621C83'),
      ('Carla Cap'  , null)
;

go

SELECT
      c.ClassName,
      s.StudentName
   from
                       #tabClass   as c
      RIGHT OUTER JOIN #tabStudent as s ON s.ClassGuid = c.ClassGuid
   --where
   --   c.ClassName LIKE '%Math%'
   order by
      c.ClassName,
      s.StudentName
   FOR
      JSON AUTO
      --, INCLUDE_NULL_VALUES
;

go

DROP TABLE IF EXISTS #tabStudent;
DROP TABLE IF EXISTS #tabClass;

go

E, em seguida, está o JSON gerado pelo SELECT anterior.And next is the JSON that is output by the preceding SELECT.

JSON_F52E2B61-18A1-11d1-B105-00805F49916B

[
   {"s":[{"StudentName":"Carla Cap"}]},
   {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]},
   {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]},
   {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]}
]

Mais informações sobre o FOR JSON AUTOMore info about FOR JSON AUTO

Para obter informações e exemplos mais detalhados, consulte Formato de saída JSON automático com o modo AUTO (SQL Server).For more detailed info and examples, see Format JSON Output Automatically with AUTO Mode (SQL Server).

Para sintaxe e uso, consulte Cláusula FOR (Transact-SQL).For syntax and usage, see FOR Clause (Transact-SQL).

Controlar outras opções de saída JSONControl other JSON output options

Controlar a saída da cláusula FOR JSON usando as seguintes opções adicionais.Control the output of the FOR JSON clause by using the following additional options.

• ROOT.ROOT. Para adicionar um único elemento de nível superior à saída JSON, especifique a opção ROOT .To add a single, top-level element to the JSON output, specify the ROOT option. Se você não especificar essa opção, a saída JSON não terá um elemento raiz.If you don't specify this option, the JSON output doesn't have a root element. Para obter mais informações, consulte Add a Root Node to JSON Output with the ROOT Option (SQL Server) (Adicionar um nó raiz à saída JSON com a opção ROOT (SQL Server)).For more info, see Add a Root Node to JSON Output with the ROOT Option (SQL Server).

• INCLUDE_NULL_VALUES.INCLUDE_NULL_VALUES. Para incluir valores nulos na saída JSON, especifique a opção INCLUDE_NULL_VALUES .To include null values in the JSON output, specify the INCLUDE_NULL_VALUES option. Se você não especificar essa opção, a saída não incluirá propriedades JSON para valores NULL nos resultados da consulta.If you don't specify this option, the output doesn't include JSON properties for NULL values in the query results. Para obter mais informações, consulte Incluir valores Null na saída JSON com a opção INCLUDE_NULL_VALUES (SQL Server).For more info, see Include Null Values in JSON Output with the INCLUDE_NULL_VALUES Option (SQL Server).

• WITHOUT_ARRAY_WRAPPER.WITHOUT_ARRAY_WRAPPER. Para remover, por padrão, os colchetes que envolvem a saída JSON da cláusula FOR JSON , especifique a opção WITHOUT_ARRAY_WRAPPER .To remove the square brackets that surround the JSON output of the FOR JSON clause by default, specify the WITHOUT_ARRAY_WRAPPER option. Use esta opção para gerar um único objeto JSON como saída de um resultado de linha única.Use this option to generate a single JSON object as output from a single-row result. Se você não especificar essa opção, a saída JSON será formatada como uma matriz, ou seja, ela será colocada entre colchetes.If you don't specify this option, the JSON output is formatted as an array - that is, it's enclosed within square brackets. Para obter mais informações, consulte Remove Square Brackets from JSON Output with the WITHOUT_ARRAY_WRAPPER Option (SQL Server) (Remover os colchetes da saída JSON com a opção WITHOUT_ARRAY_WRAPPER (SQL Server)).For more info, see Remove Square Brackets from JSON Output with the WITHOUT_ARRAY_WRAPPER Option (SQL Server).

Saída da cláusula FOR JSONOutput of the FOR JSON clause

A saída da cláusula FOR JSON tem as seguintes características:The output of the FOR JSON clause has the following characteristics:

1. O conjunto de resultados contém uma única coluna.The result set contains a single column.

   • Um conjunto de resultados pequeno pode conter uma única linha.A small result set may contain a single row.
   • Um conjunto de resultados grande divide a cadeia de caracteres JSON longa em várias linhas.A large result set splits the long JSON string across multiple rows.
     • Por padrão, o SSMS (SQL Server Management Studio) concatena os resultados em uma única linha quando a configuração de saída é Resultados em Grade.By default, SQL Server Management Studio (SSMS) concatenates the results into a single row when the output setting is Results to Grid. A barra de status do SSMS exibe a contagem de linhas real.The SSMS status bar displays the actual row count.
     • Outros aplicativos cliente podem exigir o código para recombinar os resultados longos em uma cadeia de caracteres JSON única e válida, concatenando os conteúdos de várias linhas.Other client applications may require code to recombine lengthy results into a single, valid JSON string by concatenating the contents of multiple rows. Para obter um exemplo do código em um aplicativo C#, consulte Usar a saída FOR JSON em um aplicativo cliente C#.For an example of this code in a C# application, see Use FOR JSON output in a C# client app.

   

2. Os resultados são formatados como uma matriz de objetos JSON.The results are formatted as an array of JSON objects.

   • O número de elementos na matriz JSON é igual ao número de linhas nos resultados da instrução SELECT (antes da cláusula FOR JSON ser aplicada).The number of elements in the JSON array is equal to the number of rows in the results of the SELECT statement (before the FOR JSON clause is applied).

   • Cada linha nos resultados da instrução SELECT (antes da cláusula FOR JSON ser aplicada) se torna um objeto JSON separado na matriz.Each row in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a separate JSON object in the array.

   • Cada coluna nos resultados da instrução SELECT (antes da cláusula FOR JSON ser aplicada) se torna uma propriedade do objeto JSON.Each column in the results of the SELECT statement (before the FOR JSON clause is applied) becomes a property of the JSON object.

3. Os dois os nomes das colunas e seus valores são escapados de acordo com a sintaxe JSON.Both the names of columns and their values are escaped according to JSON syntax. Para obter mais informações, consulte How FOR JSON escapes special characters and control characters (SQL Server) (Como o FOR JSON ignora os caracteres especiais e os caracteres de controle (SQL Server)).For more info, see How FOR JSON escapes special characters and control characters (SQL Server).

ExemploExample

Veja este exemplo que demonstra como a cláusula FOR JSON formata a saída JSON.Here's an example that demonstrates how the FOR JSON clause formats the JSON output.

Resultados da consultaQuery results

Saída JSONJSON output

[{
    "A": 10,
    "B": 11,
    "C": 12,
    "D": "X"
}, {
    "A": 20,
    "B": 21,
    "C": 22,
    "D": "Y"
}, {
    "A": 30,
    "B": 31,
    "C": 32,
    "D": "Z"
}] 

Para obter mais informações sobre o que você vê na saída da cláusula FOR JSON, consulte os seguintes tópicos.For more info about what you see in the output of the FOR JSON clause, see the following topics.

• How FOR JSON converts SQL Server data types to JSON data types (SQL Server) (Como FOR JSON converte tipos de dados do SQL Server para tipos de dados (SQL Server))How FOR JSON converts SQL Server data types to JSON data types (SQL Server)
  A cláusula FOR JSON usa as regras descritas neste tópico para converter os tipos de dados SQL em tipos JSON na saída JSON.The FOR JSON clause uses the rules described in this topic to convert SQL data types to JSON types in the JSON output.

• How FOR JSON escapes special characters and control characters (SQL Server) (Como o FOR JSON ignora os caracteres especiais e os caracteres de controle (SQL Server))How FOR JSON escapes special characters and control characters (SQL Server)
  A cláusula FOR JSON ignora os caracteres especiais e representa os caracteres de controle na saída JSON, conforme descrito neste tópico.The FOR JSON clause escapes special characters and represents control characters in the JSON output as described in this topic.

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 ao suporte interno para JSON no SQL Server e no Banco de Dados SQL do Azure, consulte os seguintes vídeos:For a visual introduction to the built-in JSON support in SQL Server and Azure SQL Database, see the following videos:

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

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

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

Consulte TambémSee Also

Cláusula FOR (Transact-SQL) FOR Clause (Transact-SQL)
Use FOR JSON output in SQL Server and in client apps (SQL Server) (Usar a saída FOR JSON no SQL Server e em aplicativos cliente (SQL Server))Use FOR JSON output in SQL Server and in client apps (SQL Server)