Partilhar via

Analise e transforme dados JSON com OPENJSON

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

A função OPENJSON rowset converte texto JSON em um conjunto de linhas e colunas. Depois de transformar uma coleção JSON em um conjunto de linhas com OPENJSON, você pode executar qualquer consulta SQL nos dados retornados ou inseri-la 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 usa um único objeto JSON ou uma coleção de objetos JSON e os transforma em uma ou mais linhas. Por padrão, a função OPENJSON retorna os seguintes dados:

  • A partir de um objeto JSON, a função retorna todos os pares chave/valor que encontra no primeiro nível.
  • A partir de uma matriz JSON, a função retorna todos os elementos da matriz com seus índices.

Você pode adicionar uma cláusula WITH opcional para fornecer um esquema que defina explicitamente a estrutura da saída.

OPENJSON com saída padrão

Quando você usa 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 seguintes três colunas:

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

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

O exemplo a seguir usa 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);

Aqui está o conjunto de resultados.

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

Para mais informações e exemplos, veja Utilize OPENJSON com o esquema padrão.

Para sintaxe e uso, consulte OPENJSON.

Saída OPENJSON com uma estrutura explícita

Quando você especifica um esquema para os resultados usando a cláusula WITH da função OPENJSON, a função retorna uma tabela com apenas as colunas que você define na cláusula WITH. Na cláusula WITH opcional, você especifica um conjunto de colunas de saída, seus tipos e os caminhos das propriedades de origem JSON para cada valor de saída. OPENJSON itera através da matriz de objetos JSON, lê o valor no caminho especificado para cada coluna e converte o valor para o 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'
);

Aqui está o conjunto de resultados.

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

Esta 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 de matriz no caminho especificado para o tipo especificado. Neste exemplo, os valores para a coluna Date são retirados de cada elemento no caminho $.Order.Date e convertidos em valores datetime.

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

Para sintaxe e uso, consulte OPENJSON.

OPENJSON requer nível de compatibilidade 130

A função OPENJSON está disponível apenas sob o nível de compatibilidade 130 ou 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 JSON integradas estão disponíveis em todos os níveis de compatibilidade.

Você pode verificar o nível de compatibilidade na 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