Formatar resultados da consulta como JSON com FOR JSON

Aplica-se a: SQL Server 2016 (13.x) e versões posterioresAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (apenas serverless SQL pool)endpoint de análise SQL no Microsoft FabricArmazém de Dados no Microsoft FabricBase de dados SQL no Microsoft Fabric

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 aplicativos cliente delegando a formatação da saída JSON do aplicativo ao SQL Server.

Note

do Azure Data Studio é o editor de consultas recomendado para consultas JSON porque formata automaticamente os resultados JSON, conforme mostrado neste artigo. O SQL Server Management Studio exibe uma cadeia de caracteres não formatada.

No Fabric Data Warehouse, FOR JSON deve ser o último operador na consulta e, portanto, não é permitido dentro de subconsultas.

Formatar resultados da consulta

Ao usar a cláusula FOR JSON, você pode especificar a estrutura da saída JSON explicitamente ou deixar 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 automaticamente a saída JSON baseando-se na estrutura da instrução SELECT, use FOR JSON AUTO.

Aqui está um exemplo de uma instrução SELECT com a cláusula FOR JSON e o seu resultado.

PARA JSON PATH

Controle a 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.

Aqui está uma consulta de exemplo 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 raiz nomeado.

Mais informações sobre FOR JSON PATH

Para obter informações mais detalhadas e exemplos, consulte Formatar a saída JSON aninhada utilizando o modo PATH.

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

PARA JSON AUTO

A instrução SELECT controla a saída 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 INCLUDE_NULL_VALUES para alterar esse comportamento.

Aqui está uma consulta de exemplo 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 seguinte de SELECT...FOR JSON AUTO inclui uma apresentação de como os resultados JSON se parecem quando existe uma relação um para muitos entre dados provenientes de tabelas associadas.

A ausência do valor nulo do 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 que é 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 PARA JSON AUTO

Para obter informações mais detalhadas e exemplos, consulte Formatar saída JSON automaticamente com o modo AUTO.

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

Controlar outras opções de saída JSON

Controle a saída da cláusula FOR JSON, usando as seguintes opções extras.

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

• 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 NULL valores nos resultados da consulta. Para obter mais informações, consulte Incluir valores nulos em JSON - opção INCLUDE_NULL_VALUES.

• WITHOUT_ARRAY_WRAPPER

  Para remover os colchetes que cercam a saída JSON da cláusula FOR JSON por padrão, especifique a opção WITHOUT_ARRAY_WRAPPER. Use essa opção para gerar um único objeto JSON como saída de um resultado de uma única linha. 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. Para obter mais informações, consulte Remover colchetes do 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 pequeno conjunto de resultados pode conter uma única linha.
   • Um grande conjunto de resultados divide a cadeia de caracteres JSON longa em várias linhas.

     • Por padrão, o SQL Server Management Studio (SSMS) concatena os resultados numa única linha quando a definição de saída é Resultados para Grelha. A barra de status do SSMS exibe a contagem real de linhas.

     • Outros aplicativos cliente podem exigir código para recombinar resultados longos em uma única cadeia de caracteres JSON válida concatenando o conteúdo de várias linhas. Para obter um exemplo desse 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 aplicação da cláusula FOR JSON).

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

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

3. Tanto os nomes das colunas como os respetivos valores são processados de acordo com a sintaxe JSON. Para saber mais, veja Como FOR JSON escapa de caracteres especiais e caracteres de controle.

Example

Este é 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 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 o FOR JSON converte tipos de dados do SQL Server em tipos de dados JSON
• Como FOR JSON escapa de caracteres especiais e caracteres de controle
• Vídeo: JSON como ponte entre NoSQL e mundos relacionais
• SELECT - Cláusula FOR (Transact-SQL)
• Usar a saída FOR JSON no Mecanismo de Banco de Dados SQL e em aplicativos cliente