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

Aplica-se a:sim 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.

Diagrama mostrando como o FOR JSON funciona.

Opção 1 – 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.

Diagrama do fluxo da saída FOR JSON.

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, 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 SELECT...FOR JSON AUTO seguir inclui uma exibição de como são os resultados JSON quando há uma relação de 1:00 entre 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 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.

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

        Captura de tela da saída FOR JSON no SQL Server Management Studio.

  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

A B C D
10 11 12 X
20 21 22 S
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"
}]

Confira também

Próximas etapas