Formatar os resultados da consulta como JSON com o FOR JSON (SQL Server)

Aplica-se a:yes SQL Server 2016 (13.x) e posterior

Formate os resultados da consulta como JSON ou exporte dados do SQL Server como JSON, adicionando a cláusula FOR JSON a uma instrução SELECT. Use a cláusula FOR JSON para simplificar os aplicativos cliente ao delegar a formatação da saída JSON do aplicativo para SQL Server.

Observação

O Azure Data Studio é o editor de consulta recomendado para consultas JSON porque ele formata automaticamente os resultados JSON (como visto neste artigo) em vez de exibir uma cadeia de caracteres simples.

Ao usar a cláusula FOR JSON, você pode especificar explicitamente a estrutura da saída JSON ou permitir que a estrutura da instrução SELECT determine a saída.

  • Para manter o controle total sobre o formato da saída JSON, use FOR JSON PATH. Você pode criar objetos wrapper e aninhar propriedades complexas.

  • Para formatar a saída JSON automaticamente com base na estrutura da instrução SELECT, use FOR JSON AUTO.

Veja abaixo um exemplo de uma instrução SELECT com a cláusula FOR JSON e a respectiva saída.

FOR JSON

Opção 1 – Controle de saída com FOR JSON PATH

No modo PATH, você pode usar a sintaxe de ponto, por exemplo, 'Item.UnitPrice', para formatar a saída aninhada.

Veja um exemplo de consulta que usa o modo PATH com a cláusula FOR JSON. O exemplo a seguir também usa a opção ROOT para especificar um elemento de raiz nomeado.

Diagram of flow of FOR JSON output

Mais informações sobre o FOR JSON PATH

Para obter informações e exemplos mais detalhados, consulte Formatar Saída JSON Aninhada com o Modo PATH (SQL Server).

Para sintaxe e uso, consulte a Cláusula FOR (Transact-SQL).

Opção 2 – Saída dos controles de instrução SELECT com FOR JSON AUTO

No modo AUTO , a estrutura da instrução SELECT determina o formato da saída JSON.

Por padrão, os valores nulos não são incluídos na saída. Você pode usar o INCLUDE_NULL_VALUES para alterar esse comportamento.

Veja um exemplo de consulta que usa o modo AUTO com a cláusula FOR JSON.

SELECT name, surname  
FROM emp  
FOR JSON AUTO;

E aqui está o JSON retornado.

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

2.b – exemplo de JOIN e 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.

A ausência do valor nulo no JSON retornado também é ilustrada. No entanto, você pode substituir esse comportamento padrão usando a palavra-chave INCLUDE_NULL_VALUES na cláusula FOR.

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
   ORDER BY 
      c.ClassName,
      s.StudentName
   FOR
      JSON AUTO
      -- To include NULL values in the output, uncomment the following line:
      --, 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.

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 AUTO

Para obter informações e exemplos mais detalhados, consulte Formatar saída JSON automaticamente com o modo AUTO (SQL Server).

Para sintaxe e uso, consulte a Cláusula FOR (Transact-SQL).

Controlar outras opções de saída JSON

Controle a saída da cláusula FOR JSON usando as opções adicionais a seguir.

  • ROOT. Para adicionar um único elemento de nível superior à saída JSON, especifique a opção ROOT . Se você não especificar essa opção, a saída JSON não terá um elemento raiz. Para obter mais informações, consulte Adicionar um nó raiz à saída JSON com a opção ROOT (SQL Server).

  • INCLUDE_NULL_VALUES. Para incluir valores nulos na saída JSON, especifique a opção INCLUDE_NULL_VALUES . Se você não especificar essa opção, a saída não incluirá propriedades JSON para valores NULL nos resultados da consulta. Para obter mais informações, consulte Incluir valores nulos na saída JSON com a opção INCLUDE_NULL_VALUES (SQL Server).

  • 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. Use esta opção para gerar um único objeto JSON como saída de um resultado de linha única. Se você não especificar essa opção, a saída JSON será formatada como uma matriz, ou seja, ela será colocada entre colchetes. Para obter mais informações, consulte Remover colchetes da saída JSON com a opção WITHOUT_ARRAY_WRAPPER (SQL Server).

Saída da cláusula FOR JSON

A saída da cláusula FOR JSON tem as seguintes características:

  1. O conjunto de resultados contém uma única coluna.

    • Um conjunto de resultados pequeno pode conter uma única linha.

    • Um conjunto de resultados grande divide a cadeia de caracteres JSON longa em várias linhas.

      • 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. A barra de status do SSMS exibe a contagem de linhas real.
      • 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. Para obter um exemplo do código em um aplicativo C#, consulte Usar a saída FOR JSON em um aplicativo cliente C#.

      Example of FOR JSON output

  2. Os resultados são formatados como uma matriz de objetos JSON.

    • 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).

    • Cada linha nos resultados da instrução SELECT (antes da cláusula FOR JSON ser aplicada) se torna um objeto JSON separado na matriz.

    • Cada coluna nos resultados da instrução SELECT (antes da cláusula FOR JSON ser aplicada) se torna uma propriedade do objeto JSON.

  3. Os dois os nomes das colunas e seus valores são escapados de acordo com a sintaxe JSON. Para obter mais informações, consulte Como FOR JSON escapa caracteres especiais e caracteres de controle (SQL Server).

Exemplo

Veja abaixo um exemplo que demonstra como a cláusula FOR JSON formata a saída JSON.

Resultados da consulta

Um B C D
10 11 12 X
20 21 22 S
30 31 32 Z
       

Saída JSON

[{
    "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, confira os seguintes artigos:

Saiba mais sobre JSON no SQL Server e no Banco de Dados SQL do Azure

Vídeos da Microsoft

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:

Próximas etapas