Formatar os resultados da consulta como JSON com o FOR JSON (SQL Server)
Aplica-se a:
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.
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.
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:
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#.
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.
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:
Como FOR JSON converte tipos de dados do SQL Server para tipos de dados JSON (SQL Server)
A cláusulaFOR JSONusa as regras descritas neste artigo para converter os tipos de dados SQL em tipos JSON na saída JSON.Como o FOR JSON ignora os caracteres especiais e os caracteres de controle (SQL Server)
A cláusulaFOR JSONescapa os caracteres especiais e representa os caracteres de controle na saída JSON, conforme descrito neste artigo.
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: