Analisar e transformar dados JSON com OPENJSON

  • Artigo

Aplica-se a: SQL Server 2016 (13.x) e posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics

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, veja 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.

Número Data Cliente Quantidade
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 o esquema explícito (SQL Server).

Para sintaxe e uso, veja 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