Partilhar via

Dados JSON no SQL Server

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSQL database in Microsoft Fabric

Este artigo fornece uma visão geral do formato de dados textuais JSON no SQL Server, Banco de Dados SQL do Azure, Instância Gerenciada SQL do Azure, Azure Synapse Analytics e banco de dados SQL no Microsoft Fabric.

Note

O suporte a JSON requer nível de compatibilidade de banco de dados 130 ou superior.

Overview

JSON é um formato de dados textuais popular que é usado para a troca de dados em aplicações web e móveis modernas. JSON também é usado para armazenar dados não estruturados em arquivos de log ou bancos de dados NoSQL, como o Microsoft Azure Cosmos DB. Muitos serviços Web REST retornam resultados formatados como texto JSON ou aceitam dados formatados como JSON. Por exemplo, a maioria dos serviços do Azure, como o Azure Search, o Armazenamento do Azure e o Azure Cosmos DB, tem pontos de extremidade REST que retornam ou consomem JSON. JSON também é o principal formato para a troca de dados entre páginas da Web e servidores Web usando chamadas AJAX.

As funções JSON, introduzidas pela primeira vez no SQL Server 2016 (13.x), permitem combinar conceitos NoSQL e relacionais no mesmo banco de dados. Você pode combinar colunas relacionais clássicas com colunas que contêm documentos formatados como texto JSON na mesma tabela, analisar e importar documentos JSON em estruturas relacionais ou formatar dados relacionais para texto JSON.

Segue-se um exemplo de texto JSON:

[
    {
        "name": "John",
        "skills": [ "SQL", "C#", "Azure" ]
    },
    {
        "name": "Jane",
        "surname": "Doe"
    }
]

Usando funções e operadores internos do SQL Server, você pode fazer o seguinte com texto JSON:

  • Analise o texto JSON e leia ou modifique valores.
  • Transforme matrizes de objetos JSON em formato de tabela.
  • Execute qualquer consulta Transact-SQL nos objetos JSON convertidos.
  • Formate os resultados de consultas Transact-SQL no formato JSON.

Diagrama mostrando a visão geral do suporte JSON interno.

Alterações do SQL Server 2025

O SQL Server 2025 (17.x) introduz as seguintes melhorias em JSON, todas atualmente em pré-visualização:

Principais recursos JSON

As próximas seções discutem os principais recursos que o SQL Server fornece com seu suporte JSON interno.

Tipo de dados JSON

Note

O tipo de dados JSON:

  • está geralmente disponível para o Banco de Dados SQL do Azure e a Instância Gerenciada SQL do Azure com a política de atualizaçãodo SQL Server 2025 ou Always-up-to-date.
  • está em pré-visualização para o SQL Server 2025 (17.x) e a base de dados SQL em Fabric.

O novo tipo de dados json que armazena documentos JSON em um formato binário nativo que fornece os seguintes benefícios sobre o armazenamento de dados JSON em varchar/nvarchar:

  • Leituras mais eficientes, pois o documento já está analisado
  • Gravações mais eficientes, pois a consulta pode atualizar valores individuais sem acessar o documento inteiro
  • Armazenamento mais eficiente, otimizado para compactação
  • Nenhuma alteração na compatibilidade com o código existente

Usar as mesmas funções JSON descritas neste artigo continua sendo a maneira mais eficiente de consultar o tipo de dados json . Para obter mais informações sobre o tipo de dados json nativo, consulte Tipo de dados JSON.

Extrair valores do texto JSON e usá-los em consultas

Se você tiver texto JSON armazenado em tabelas de banco de dados, poderá ler ou modificar valores no texto JSON usando as seguintes funções internas:

  • O ISJSON testa se uma cadeia contém JSON válido.
  • JSON_VALUE extrai um valor escalar de uma cadeia JSON.
  • JSON_QUERY extrai um objeto ou um array de uma string JSON.
  • JSON_MODIFY altera um valor numa cadeia JSON.

Example

No exemplo a seguir, a consulta usa dados relacionais e JSON (armazenados em uma coluna chamada jsonCol) de uma tabela chamada People:

SELECT Name,
       Surname,
       JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
       JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' ' +
           JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
       JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
      AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
      AND STATUS = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode');

Aplicativos e ferramentas não veem diferença entre os valores retirados de colunas de tabelas escalares e os valores retirados de colunas JSON. Você pode usar valores de texto JSON em qualquer parte de uma consulta Transact-SQL (incluindo cláusulas WHERE, ORDER BY ou GROUP BY, agregações de janela e assim por diante). As funções JSON usam sintaxe semelhante a JavaScript para referenciar valores dentro do texto JSON.

Para mais informações, consulte Validar, consultar e alterar dados JSON com funções incorporadas, JSON_VALUE e JSON_QUERY.

Alterar valores JSON

Se tiver de modificar partes do texto JSON, pode usar a função JSON_MODIFY para atualizar o valor de uma propriedade numa cadeia JSON e devolver a cadeia JSON atualizada. O exemplo a seguir atualiza o valor de uma propriedade em uma variável que contém JSON:

DECLARE @json AS NVARCHAR (MAX);

SET @json = '{"info": {"address": [{"town": "Belgrade"}, {"town": "Paris"}, {"town":"Madrid"}]}}';
SET @json = JSON_MODIFY(@json, '$.info.address[1].town', 'London');

SELECT @json AS modifiedJson;

Aqui está o conjunto de resultados.

{"info":{"address":[{"town":"Belgrade"},{"town":"London"},{"town":"Madrid"}]}}

Converter coleções JSON em um conjunto de linhas

Você não precisa de uma linguagem de consulta personalizada para consultar JSON no SQL Server. Para consultar dados JSON, você pode usar o T-SQL padrão. Se você precisar criar uma consulta ou relatório em dados JSON, poderá converter facilmente dados JSON em linhas e colunas chamando a OPENJSON função de conjunto de linhas. Para mais informações, consulte Parse and transform JSON data with OPENJSON.

O exemplo a seguir chama OPENJSON e transforma a matriz de objetos armazenada na @json variável em um conjunto de linhas que pode ser consultado com uma instrução Transact-SQL SELECT padrão:

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith"}, "dob": "2005-11-04T12:00:00"}
]';

SELECT *
FROM OPENJSON (@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR (50) '$.info.name',
    lastName NVARCHAR (50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob'
);

Aqui está o conjunto de resultados.

ID firstName lastName age dateOfBirth
2 John Smith 25
5 Jane Smith 2005-11-04T12:00:00

OPENJSON transforma a matriz de objetos JSON em uma tabela na qual cada objeto é representado como uma linha e os pares chave/valor são retornados como células. A saída observa as seguintes regras:

  • OPENJSON converte valores JSON para os tipos especificados na WITH cláusula.
  • OPENJSON É capaz de lidar com pares de chave/valor simples e objetos aninhados e organizados hierarquicamente.
  • Não é necessário retornar todos os campos contidos no texto JSON.
  • Se os valores JSON não existirem, OPENJSON retornará NULL os valores.
  • Opcionalmente, pode-se especificar um caminho após a definição do tipo para referenciar uma propriedade aninhada ou para referenciar uma propriedade com um nome diferente.
  • O prefixo opcional strict no caminho especifica que os valores para as propriedades especificadas devem existir no texto JSON.

Para mais informações, veja Análise e transformação de dados JSON com OPENJSON e OPENJSON.

Os documentos JSON podem ter subelementos e dados hierárquicos que não podem ser mapeados diretamente nas colunas relacionais padrão. Nesse caso, você pode nivelar a hierarquia JSON unindo a entidade pai com submatrizes.

No exemplo a seguir, o segundo objeto na matriz tem uma submatriz que representa as habilidades da pessoa. Cada subobjeto pode ser analisado usando chamada de função adicional OPENJSON :

DECLARE @json AS NVARCHAR (MAX);

SET @json = N'[
  {"id": 2, "info": {"name": "John", "surname": "Smith"}, "age": 25},
  {"id": 5, "info": {"name": "Jane", "surname": "Smith", "skills": ["SQL", "C#", "Azure"]}, "dob": "2005-11-04T12:00:00"}
]';

SELECT id,
       firstName,
       lastName,
       age,
       dateOfBirth,
       skill
FROM OPENJSON (@json) WITH (
    id INT 'strict $.id',
    firstName NVARCHAR (50) '$.info.name',
    lastName NVARCHAR (50) '$.info.surname',
    age INT,
    dateOfBirth DATETIME2 '$.dob',
    skills NVARCHAR (MAX) '$.info.skills' AS JSON
)
OUTER APPLY OPENJSON (skills) WITH (skill NVARCHAR (8) '$');

A skills matriz é retornada no primeiro OPENJSON como fragmento de texto JSON original e, usando o OPENJSON operador, é passada para outra APPLY função. A segunda função OPENJSON analisa a matriz JSON e retorna os valores da cadeia de caracteres como um conjunto de linhas de coluna única, que será unido ao resultado da primeira OPENJSON.

Aqui está o conjunto de resultados.

ID firstName lastName age dateOfBirth competência
2 John Smith 25
5 Jane Smith 2005-11-04T12:00:00 SQL
5 Jane Smith 2005-11-04T12:00:00 C#
5 Jane Smith 2005-11-04T12:00:00 Azure

OUTER APPLY OPENJSON Junta entidade de nível superior com subconjunto e retorna conjunto de resultados simplificado. Devido ao JOIN, a segunda linha é repetida para cada habilidade.

Converter dados do SQL Server em JSON ou exportar JSON

Note

Não é suportado converter dados do Azure Synapse Analytics para JSON ou exportar JSON.

Formate os dados do SQL Server ou os resultados das consultas SQL como JSON adicionando a FOR JSON cláusula a uma SELECT instrução. Use FOR JSON para delegar a formatação da saída JSON de seus aplicativos cliente ao SQL Server. Para obter mais informações, consulte Formatar resultados de consulta como JSON com FOR JSON.

O exemplo a seguir usa o modo PATH com a FOR JSON cláusula:

SELECT id,
       firstName AS "info.name",
       lastName AS "info.surname",
       age,
       dateOfBirth AS dob
FROM People
FOR JSON PATH;

A FOR JSON cláusula formata os resultados SQL como texto JSON que pode ser fornecido a qualquer aplicativo que entenda JSON. A opção PATH usa aliases separados por pontos na cláusula SELECT para aninhar objetos nos resultados da consulta.

Aqui está o conjunto de resultados.

[
  {
    "id": 2,
    "info": {
      "name": "John",
      "surname": "Smith"
    },
    "age": 25
  },
  {
    "id": 5,
    "info": {
      "name": "Jane",
      "surname": "Smith"
    },
    "dob": "2005-11-04T12:00:00"
  }
]

Para obter mais informações, consulte Formatar resultados da consulta como JSON com a cláusula FOR JSON e SELECT - FOR.

Dados JSON de agregados

As funções de agregação JSON permitem a construção de objetos ou matrizes JSON com base em uma agregação de dados SQL.

  • JSON_OBJECTAGG constrói um objeto JSON a partir de uma agregação de dados ou colunas SQL.
  • JSON_ARRAYAGG constrói uma matriz JSON a partir de uma agregação de dados ou colunas SQL.

Note

As funções agregadas JSON_OBJECTAGG e JSON_ARRAYAGG estão geralmente disponíveis para Azure SQL Database, Azure SQL Managed Instance (com SQL Server 2025 ou Always-up-to-date política de atualização), Fabric Data Warehouse, e em pré-visualização para SQL Server 2025 (17.x).

Casos de uso para dados JSON no SQL Server

O suporte a JSON no SQL Server e no Banco de Dados SQL do Azure permite combinar conceitos relacionais e NoSQL. Você pode facilmente transformar dados relacionais em semiestruturados e vice-versa. No entanto, o JSON não substitui os modelos relacionais existentes. Aqui estão alguns casos de uso específicos que se beneficiam do suporte JSON no SQL Server e no Banco de dados SQL.

  • Simplifique modelos de dados complexos

    Considere desnormalizar seu modelo de dados com campos JSON no lugar de várias tabelas filhas.

  • Armazene dados de varejo e comércio eletrônico

    Armazene informações sobre produtos com uma ampla gama de atributos variáveis em um modelo desnormalizado para flexibilidade.

  • Processar dados de log e telemetria

    Carregue, consulte e analise dados de log armazenados como arquivos JSON com todo o poder da linguagem Transact-SQL.

  • Armazenar dados semiestruturados da IoT

    Quando você precisar de análise em tempo real de dados de IoT, carregue os dados de entrada diretamente no banco de dados em vez de prepará-los em um local de armazenamento.

  • Simplifique o desenvolvimento da API REST

    Transforme dados relacionais de seu banco de dados facilmente no formato JSON usado pelas APIs REST que suportam seu site.

Combinar dados relacionais e JSON

O SQL Server fornece um modelo híbrido para armazenar e processar dados relacionais e JSON usando a linguagem Transact-SQL padrão. Você pode organizar coleções de seus documentos JSON em tabelas, estabelecer relações entre eles, combinar colunas escalares fortemente tipadas armazenadas em tabelas com pares flexíveis de chave/valor armazenados em colunas JSON e consultar valores escalares e JSON em uma ou mais tabelas usando Transact-SQL completo.

O texto JSON é armazenado em colunas varchar ou nvarchar e é indexado como texto sem formatação. Qualquer recurso ou componente do SQL Server que ofereça suporte a texto dá suporte a JSON, portanto, quase não há restrições na interação entre JSON e outros recursos do SQL Server. Você pode armazenar JSON em tabelas In-memory ou Temporal, aplicar predicados de Segurança Row-Level em texto JSON e assim por diante.

Aqui estão alguns casos de uso que mostram como você pode usar o suporte JSON interno no SQL Server.

Armazenar e indexar dados JSON no SQL Server

JSON é um formato textual para que os documentos JSON possam ser armazenados em colunas nvarchar em um Banco de Dados SQL. Como o tipo nvarchar é suportado em todos os subsistemas do SQL Server, você pode colocar documentos JSON em tabelas com índices columnstore clusterizados, tabelas otimizadas para memória ou arquivos externos que podem ser lidos usando OPENROWSET ou PolyBase.

Para saber mais sobre suas opções de armazenamento, indexação e otimização de dados JSON no SQL Server, consulte os seguintes artigos:

Carregar arquivos JSON no SQL Server

Você pode formatar informações armazenadas em arquivos como JSON padrão ou JSON delimitado por linha. O SQL Server pode importar o conteúdo de arquivos JSON, analisá-lo usando as OPENJSON funções ou JSON_VALUE e carregá-lo em tabelas.

  • Se seus documentos JSON estiverem armazenados em arquivos locais, em unidades de rede compartilhadas ou em locais de Arquivos do Azure que podem ser acessados pelo SQL Server, você poderá usar a importação em massa para carregar seus dados JSON no SQL Server.

  • Se seus arquivos JSON delimitados por linha estiverem armazenados no armazenamento de Blob do Azure ou no sistema de arquivos Hadoop, você poderá usar o PolyBase para carregar texto JSON, analisá-lo em Transact-SQL código e carregá-lo em tabelas.

Importar dados JSON para tabelas do SQL Server

Se você precisar carregar dados JSON de um serviço externo para o SQL Server, poderá usar OPENJSON para importar os dados para o SQL Server em vez de analisar os dados na camada de aplicativo.

Em plataformas suportadas, use o tipo de dados json nativo em vez de nvarchar(max) para melhorar o desempenho e o armazenamento mais eficiente.

DECLARE @jsonVariable AS NVARCHAR (MAX);

SET @jsonVariable = N'[
  {
    "Order": {
      "Number":"SO43659",
      "Date":"2011-05-31T00:00:00"
    },
    "AccountNumber":"AW29825",
    "Item": {
      "Price":2024.9940,
      "Quantity":1
    }
  },
  {
    "Order": {
      "Number":"SO43661",
      "Date":"2011-06-01T00:00:00"
    },
    "AccountNumber":"AW73565",
    "Item": {
      "Price":2024.9940,
      "Quantity":3
    }
  }
]';

-- INSERT INTO <sampleTable>
SELECT SalesOrderJsonData.*
FROM OPENJSON (@jsonVariable, N'$') WITH (
    Number VARCHAR (200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR (200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData;

Você pode fornecer o conteúdo da variável JSON por um serviço REST externo, enviá-lo como um parâmetro de uma estrutura JavaScript do lado do cliente ou carregá-lo de arquivos externos. Você pode facilmente inserir, atualizar ou mesclar resultados de texto JSON em uma tabela do SQL Server.

Analise dados JSON com consultas SQL

Se você precisar filtrar ou agregar dados JSON para fins de relatório, poderá usar OPENJSON para transformar JSON em formato relacional. Em seguida, você pode usar Transact-SQL padrão e funções internas para preparar os relatórios.

SELECT Tab.Id,
       SalesOrderJsonData.Customer,
       SalesOrderJsonData.Date
FROM SalesOrderRecord AS Tab
CROSS APPLY OPENJSON (Tab.json, N'$.Orders.OrdersArray') WITH (
    Number VARCHAR (200) N'$.Order.Number',
    Date DATETIME N'$.Order.Date',
    Customer VARCHAR (200) N'$.AccountNumber',
    Quantity INT N'$.Item.Quantity'
) AS SalesOrderJsonData
WHERE JSON_VALUE(Tab.json, '$.Status') = N'Closed'
ORDER BY JSON_VALUE(Tab.json, '$.Group'),
         Tab.DateModified;

Você pode usar colunas de tabela padrão e valores de texto JSON na mesma consulta. Você pode adicionar índices na JSON_VALUE(Tab.json, '$.Status') expressão para melhorar o desempenho da consulta. Para obter mais informações, consulte Indexar dados JSON.

Retornar dados de uma tabela do SQL Server formatada como JSON

Se você tiver um serviço Web que obtém dados da camada de banco de dados e os retorna no formato JSON, ou se tiver estruturas ou bibliotecas JavaScript que aceitam dados formatados como JSON, poderá formatar a saída JSON diretamente em uma consulta SQL. Em vez de escrever código ou incluir uma biblioteca para converter resultados de consulta tabular e, em seguida, serializar objetos para o formato JSON, você pode usar FOR JSON para delegar a formatação JSON ao SQL Server.

Por exemplo, talvez você queira gerar uma saída JSON compatível com a especificação OData. O serviço Web espera uma solicitação e resposta no seguinte formato:

  • Pedido: /Northwind/Northwind.svc/Products(1)?$select=ProductID,ProductName

  • Resposta: {"@odata.context": "https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity", "ProductID": 1, "ProductName": "Chai"}

Este URL OData representa uma solicitação para as colunas ProductID e ProductName para o produto com ID 1. Você pode usar FOR JSON para formatar a saída conforme esperado no SQL Server.

SELECT 'https://services.odata.org/V4/Northwind/Northwind.svc/$metadata#Products(ProductID,ProductName)/$entity' AS '@odata.context',
       ProductID,
       Name AS ProductName
FROM Production.Product
WHERE ProductID = 1
FOR JSON AUTO;

A saída dessa consulta é um texto JSON totalmente compatível com a especificação OData. A formatação e a fuga são tratadas pelo SQL Server. O SQL Server também pode formatar resultados de consulta em qualquer formato, como OData JSON ou GeoJSON.

Faça um test drive no suporte JSON integrado com o banco de dados de exemplo AdventureWorks

Para obter o banco de dados de exemplo AdventureWorks, baixe pelo menos o arquivo de banco de dados e o arquivo de exemplos e scripts do GitHub.

Depois de restaurar o banco de dados de exemplo para uma instância do SQL Server, extraia o arquivo de exemplo e abra o JSON Sample Queries procedures views and indexes.sql arquivo da pasta JSON. Execute os scripts neste arquivo para reformatar alguns dados existentes como dados JSON, testar consultas de exemplo e relatórios sobre os dados JSON, indexar os dados JSON e importar e exportar JSON.

Veja o que você pode fazer com os scripts incluídos no arquivo:

  1. Desnormalize o esquema existente para criar colunas de dados JSON.

    • Armazene informações das tabelas SalesReasons, SalesOrderDetails, SalesPerson, Customer e outras que contêm informações relacionadas com encomendas de venda em colunas JSON na tabela SalesOrder_json.

    • Armazene informações da tabela EmailAddresses e da tabela PersonPhone na tabela Person_json como matrizes de objetos JSON.

  2. Crie procedimentos e exibições que consultem dados JSON.

  3. Indexar dados JSON. Crie índices em propriedades JSON e índices de texto completo.

  4. Importação e exportação de JSON. Crie e execute procedimentos que exportem o conteúdo das tabelas Person e SalesOrder como resultados JSON, e que importem e atualizem as tabelas Person e SalesOrder usando entrada JSON.

  5. Execute exemplos de consulta. Execute algumas consultas que chamam os procedimentos armazenados e modos de exibição que você criou nas etapas 2 e 4.

  6. Limpe os scripts. Não execute esta parte se quiser manter os procedimentos armazenados e as vistas que criou nos passos 2 e 4.

Conteúdo relacionado

  • Last updated on