Compartilhar via

Analisar e transformar dados JSON com OPENJSON

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics (somente pool de SQL sem servidor)endpoint de análise de SQL no Microsoft FabricArmazém no Microsoft FabricBanco de dados SQL no Microsoft Fabric

A função de conjunto de linhas OPENJSON converte o texto JSON em um conjunto de linhas e colunas. Depois de transformar uma coleção JSON em um conjunto de linhas com OPENJSON, é possível executar qualquer consulta SQL nos dados retornados ou inseri-los em uma tabela do SQL Server. Para obter mais informações sobre como trabalhar com dados JSON no Mecanismo de Banco de Dados do SQL Server, consulte Dados JSON no SQL Server.

A função OPENJSON obtém um único objeto JSON ou uma coleção de objetos JSON e transforma-os em uma ou mais linhas. Por padrão, a função OPENJSON retorna os dados a seguir:

  • De um objeto JSON, a função retorna todos os pares chave-valor localizados no primeiro nível.
  • De uma matriz JSON, a função retorna todos os elementos da matriz com seus índices.

É possível adicionar uma cláusula opcional WITH para fornecer um esquema que define explicitamente a estrutura da saída.

OPENJSON com a saída padrão

Ao usar a função OPENJSON sem fornecer um esquema explícito para os resultados, ou seja, sem uma cláusula WITH após OPENJSON, a função retorna uma tabela com as três colunas a seguir:

  1. O name da propriedade no objeto de entrada (ou o índice do elemento na matriz de entrada).
  2. O value da propriedade ou o elemento da matriz.
  3. O type (por exemplo, cadeia de caracteres, número, booliano, matriz ou objeto).

O OPENJSON retorna cada propriedade do objeto JSON ou cada elemento da matriz como uma linha separada.

O exemplo a seguir usa o OPENJSON com o esquema padrão, ou seja, sem a cláusula WITH opcional, e retorna uma linha para cada propriedade do objeto JSON.

DECLARE @json NVARCHAR(MAX);

SET @json='{ "name": "John", "surname": "Doe", "age": 45, "skills": [ "SQL", "C#", "MVC" ]}';

SELECT *
FROM OPENJSON(@json);

Veja a seguir o conjunto de resultados.

chave value tipo
name John 1
surname Doe 1
age 45 2
skills [ "SQL" ,"C#" ,"MVC" ] 4

Para obter mais informações e exemplos, veja Usar OPENJ com o esquema padrão.

Para sintaxe e uso, consulte OPENJSON.

Saída OPENJSON com uma estrutura explícita

Quando você especificar um esquema para os resultados usando a cláusula WITH da função OPENJSON, a função retornará uma tabela com apenas as colunas que você definir na cláusula WITH. Na cláusula opcional WITH, especifique um conjunto de colunas de saída, seus tipos e os caminhos das propriedades de origem do JSON para cada valor de saída. OPENJSON itera na matriz de objetos JSON, lê o valor no caminho especificado para cada coluna e converte o valor no tipo especificado.

O exemplo a seguir usa OPENJSON com um esquema para a saída que você especifica explicitamente na cláusula WITH.

DECLARE @json NVARCHAR(MAX);

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

SELECT *
FROM OPENJSON(@json) WITH (
    Number VARCHAR(200) '$.Order.Number',
    DATE DATETIME '$.Order.Date',
    Customer VARCHAR(200) '$.AccountNumber',
    Quantity INT '$.Item.Quantity'
);

Veja a seguir o conjunto de resultados.

Number Date Customer Quantity
SO43659 2024-05-31T00:00:00 AW29825 1
SO43661 2024-06-01T00:00:00 AW73565 3

Essa função retorna e formata os elementos de uma matriz JSON.

  • Para cada elemento na matriz JSON, OPENJSON gera uma nova linha na tabela de saída. Os dois elementos na matriz JSON são convertidos em duas linhas na tabela retornada.

  • Para cada coluna especificada usando a sintaxe colName type json_path, OPENJSON converte o valor encontrado em cada elemento da matriz do caminho especificado no tipo especificado. Neste exemplo, os valores para a coluna Date são tirados de cada elemento no caminho $.Order.Date e convertidos em valores de data/hora.

Para obter mais informações e exemplos, consulte Usar OPENJSON com um esquema explícito.

Para sintaxe e uso, consulte OPENJSON.

OPENJSON requer o nível de compatibilidade 130

A função OPENJSON está disponível somente no nível de compatibilidade 130 e superior. Se o nível de compatibilidade do banco de dados for inferior a 130, o SQL Server não poderá localizar e executar a função OPENJSON. Outras funções internas do JSON estão disponíveis em todos os níveis de compatibilidade.

Você pode verificar o nível de compatibilidade no modo de exibição sys.databases ou nas propriedades do banco de dados e alterar o nível de compatibilidade de um banco de dados usando o seguinte comando:

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130;

Conteúdo relacionado

  • Last updated on