Formatar os resultados da consulta como JSON com FOR JSON

Aplica-se a: SQL Server 2016 (13.x) e posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics (somente pool de SQL sem servidor)

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 o 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. O SQL Server Management Studio exibe uma cadeia de caracteres não formatada.

Formatar resultados de consulta

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.

• Formate 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 PATH

Controle de saída com FOR JSON PATH

No modo PATH, você pode usar a sintaxe de ponto, por exemplo,Item.Price 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.

Mais informações sobre o FOR JSON PATH

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

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

FOR JSON AUTO

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 NULL 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"
}]

Exemplo com JOIN e NULL

O exemplo a seguir de SELECT...FOR JSON AUTO inclui uma exibição de como são os resultados JSON quando há um relacionamento de 1:muitos entre os dados de tabelas unidas.

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.

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 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 mais informações detalhadas e exemplos, veja Formatar a Saída JSON Automaticamente com o Modo AUTO (SQL Server).

Para sintaxe e uso, veja 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. Veja mais informações em Incluir valores Null em JSON - opção INCLUDE_NULL_VALUES.

• 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, a saída será colocada entre colchetes. Veja mais informações em Remover os colchetes da saída JSON - opção WITHOUT_ARRAY_WRAPPER.

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#.

       

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, veja Como o FOR JSON ignora os caracteres especiais e os 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

A	B	C	D
10	11	12	X
20	21	22	Y
30	31	32	Z

Saída em 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"
}]

Conteúdo relacionado

• Como FOR JSON converte tipos de dados do SQL Server para tipos de dados JSON (SQL Server)
• Como o FOR JSON ignora os caracteres especiais e os caracteres de controle (SQL Server)
• JSON é uma ponte entre o NoSQL e mundos relacionais
• SELECT – Cláusula FOR (Transact-SQL)
• Usar a saída FOR JSON no SQL Server e em aplicativos cliente (SQL Server)