Partilhar via

OPENJSON (Transact-SQL)

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

A OPENJSON função com valor de tabela analisa texto JSON e retorna objetos e propriedades da entrada JSON como linhas e colunas. Em outras palavras, OPENJSON fornece uma exibição de conjunto de linhas sobre um documento JSON. Você pode especificar explicitamente as colunas no conjunto de linhas e os caminhos da propriedade JSON usados para preencher as colunas. Como OPENJSON retorna um conjunto de linhas, você pode usar OPENJSON na FROM cláusula de uma instrução Transact-SQL da mesma forma que pode usar qualquer outra tabela, exibição ou função com valor de tabela.

Use OPENJSON para importar dados JSON para o SQL Server ou para converter dados JSON em formato relacional para um aplicativo ou serviço que não pode consumir JSON diretamente.

Note

A OPENJSON função 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 OPENJSON função. Outras funções JSON estão disponíveis em todos os níveis de compatibilidade.

Você pode verificar o nível de compatibilidade na visualização sys.databases ou nas propriedades do banco de dados. Você pode alterar o nível de compatibilidade de um banco de dados com o seguinte comando:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Transact-SQL convenções de sintaxe

Syntax

OPENJSON( jsonExpression [ , path ] )  [ <with_clause> ]

<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )

A OPENJSON função com valor de tabela analisa jsonExpression fornecida como o primeiro argumento e retorna uma ou mais linhas contendo dados dos objetos JSON na expressão. jsonExpression pode conter subobjetos aninhados. Se quiser analisar um subobjeto de dentro de jsonExpression, você pode especificar um parâmetro path para o subobjeto JSON.

openjson

Diagrama da sintaxe para OPENJSON TVF.

Por padrão, a OPENJSON função com valor de tabela retorna três colunas, que contêm o nome da chave, o valor e o tipo de cada key:value par encontrado em jsonExpression. Como alternativa, você pode especificar explicitamente o esquema do conjunto de resultados que OPENJSON retorna fornecendo with_clause.

with_clause

Diagrama da sintaxe para a cláusula WITH em OPENJSON TVF.

O with_clause contém uma lista de colunas com seus tipos para OPENJSON retornar. Por padrão, OPENJSON corresponde as teclas em jsonExpression com os nomes das colunas em with_clause (neste caso, as teclas de correspondência implicam que diferencia maiúsculas de minúsculas). Se um nome de coluna não corresponder a um nome de chave, você poderá fornecer uma column_path opcional, que é uma Expressões de Caminho JSON que faz referência a uma chave dentro de jsonExpression.

Arguments

jsonExpression

É uma expressão de caractere Unicode que contém texto JSON.

OPENJSON itera sobre os elementos da matriz ou as propriedades do objeto na expressão JSON e retorna uma linha para cada elemento ou propriedade. O exemplo a seguir retorna cada propriedade do objeto fornecido como jsonExpression:

DECLARE @json NVARCHAR(2048) = N'{
   "String_value": "John",
   "DoublePrecisionFloatingPoint_value": 45,
   "DoublePrecisionFloatingPoint_value": 2.3456,
   "BooleanTrue_value": true,
   "BooleanFalse_value": false,
   "Null_value": null,
   "Array_value": ["a","r","r","a","y"],
   "Object_value": {"obj":"ect"}
}';

SELECT * FROM OpenJson(@json);

Results:

chave value tipo
String_value John 1
DoublePrecisionFloatingPoint_value 45 2
DoublePrecisionFloatingPoint_value 2.3456 2
BooleanTrue_value true 3
BooleanFalse_value false 3
Null_value NULL 0
Array_value ["A", "R", "R", "A", "Y"] 4
Object_value {"obj":"ect"} 5
  • O DoublePrecisionFloatingPoint_value está em conformidade com IEEE-754.

path

É uma expressão de caminho JSON opcional que faz referência a um objeto ou uma matriz dentro de jsonExpression. OPENJSON procura o texto JSON na posição especificada e analisa apenas o fragmento referenciado. Para obter mais informações, consulte Expressões de caminho JSON.

Você pode fornecer uma variável como o valor do caminho. (Isso não é suportado no SQL Server 2016 (13.x) e versões anteriores.)

O exemplo a seguir retorna um objeto aninhado especificando o caminho:

DECLARE @json NVARCHAR(4000) = N'{  
      "path": {  
            "to":{  
                 "sub-object":["en-GB", "en-UK","de-AT","es-AR","sr-Cyrl"]  
                 }  
              }  
 }';

SELECT [key], value
FROM OPENJSON(@json,'$.path.to."sub-object"')

Results

Key Value
0 en-GB
1 en-UK
2 de-AT
3 es-AR
4 sr-Cyrl

Quando OPENJSON analisa uma matriz JSON, a função retorna os índices dos elementos no texto JSON como chaves.

A comparação usada para corresponder as etapas do caminho com as propriedades da expressão JSON diferencia maiúsculas de minúsculas e não reconhece agrupamento (ou seja, uma comparação BIN2).

Identidade do elemento de matriz

a OPENJSON função no pool SQL sem servidor no Azure Synapse Analytics pode gerar automaticamente a identidade de cada linha que é retornada como resultado. A coluna de identidade é especificada usando a expressão $.sql:identity() no caminho JSON após a definição da coluna. A coluna com esse valor na expressão de caminho JSON gerará um número exclusivo baseado em 0 para cada elemento na matriz JSON que a função analisa. O valor da identidade representa a posição/índice do elemento da matriz.

DECLARE @array VARCHAR(MAX);
SET @array = '[{"month":"Jan", "temp":10},{"month":"Feb", "temp":12},{"month":"Mar", "temp":15},
               {"month":"Apr", "temp":17},{"month":"May", "temp":23},{"month":"Jun", "temp":27}
              ]';

SELECT * FROM OPENJSON(@array)
        WITH (  month VARCHAR(3),
                temp int,
                month_id tinyint '$.sql:identity()') as months

Results

month temp month_id
Jan 10 0
Feb 12 1
Mar 15 2
Apr 17 3
May 23 4
Jun 27 5

A identidade está disponível somente no pool SQL sem servidor no Synapse Analytics.

with_clause

Define explicitamente o esquema de saída para a OPENJSON função retornar. O with_clause opcional pode conter os seguintes elementos:

colName

O nome da coluna de saída.

Por padrão, OPENJSON usa o nome da coluna para corresponder a uma propriedade no texto JSON. Por exemplo, se você especificar a coluna name no esquema, OPENJSON tentará preencher essa coluna com a propriedade "name" no texto JSON. Você pode substituir esse mapeamento padrão usando o argumento column_path .

type

O tipo de dados para a coluna de saída.

Note

Se você também usar a AS JSON opção, o tipo de dados da coluna deve ser nvarchar(MAX).

column_path

É o caminho JSON que especifica a propriedade a ser retornada na coluna especificada. Para obter mais informações, consulte a descrição do parâmetro path anteriormente neste tópico.

Use column_path para substituir as regras de mapeamento padrão quando o nome de uma coluna de saída não corresponder ao nome da propriedade.

A comparação usada para corresponder as etapas do caminho com as propriedades da expressão JSON diferencia maiúsculas de minúsculas e não reconhece agrupamento (ou seja, uma comparação BIN2).

Para saber mais sobre caminhos, veja Expressões de caminho JSON.

COMO JSON

Use a AS JSON opção em uma definição de coluna para especificar que a propriedade referenciada contém um objeto JSON interno ou matriz. Se você especificar a AS JSON opção, o tipo da coluna deve ser nvarchar(MAX).

  • Se você não especificar AS JSON para uma coluna, a função retornará um valor escalar (por exemplo, int, string, true, false) da propriedade JSON especificada no caminho especificado. Se o caminho representar um objeto ou uma matriz, e a propriedade não puder ser encontrada no caminho especificado, a função retornará NULL no lax modo ou retornará um erro no strict modo. Esse comportamento é semelhante ao comportamento da JSON_VALUE função.

  • Se você especificar AS JSON para uma coluna, a função retornará um fragmento JSON da propriedade JSON especificada no caminho especificado. Se o caminho representar um valor escalar e a propriedade não puder ser encontrada no caminho especificado, a função retornará NULL no lax modo ou retornará um erro no strict modo. Esse comportamento é semelhante ao comportamento da JSON_QUERY função.

Note

Se você quiser retornar um fragmento JSON aninhado de uma propriedade JSON, será necessário fornecer o AS JSON sinalizador. Sem essa opção, se a propriedade não puder ser encontrada, OPENJSON retornará um NULL valor em vez do objeto ou matriz JSON referenciada ou retornará um erro em tempo de execução no strict modo.

Por exemplo, a consulta a seguir retorna e formata os elementos de uma matriz:

DECLARE @json NVARCHAR(MAX) = 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  
    }  
  }
]'  

SELECT *
FROM OPENJSON ( @json )  
WITH (   
              Number   VARCHAR(200)   '$.Order.Number',  
              Date     DATETIME       '$.Order.Date',  
              Customer VARCHAR(200)   '$.AccountNumber',  
              Quantity INT            '$.Item.Quantity',  
              [Order]  NVARCHAR(MAX)  AS JSON  
 )

Results

Number Date Customer Quantity Order
SO43659 2011-05-31T00:00:00 AW29825 1 {"Número":"SO43659","Data":"2011-05-31T00:00:00"}
SO43661 2011-06-01T00:00:00 AW73565 3 {"Número":"SO43661","Data":"2011-06-01T00:00:00"}

Valor de retorno

As colunas que a OPENJSON função retorna dependem da WITH opção.

  • Quando você chama OPENJSON com o esquema padrão - ou seja, quando você não especifica um esquema explícito WITH na cláusula - a função retorna uma tabela com as seguintes colunas:

    • Key. Um valor nvarchar(4000) que contém o nome da propriedade especificada ou o índice do elemento na matriz especificada. A key coluna tem um agrupamento BIN2.

    • Value. Um valor nvarchar(MAX) que contém o valor da propriedade. A value coluna herda seu agrupamento de jsonExpression.

    • Type. Um valor int que contém o tipo do valor. A Type coluna é retornada somente quando você usa OPENJSON com o esquema padrão. A type coluna tem um dos seguintes valores:

      Valor da coluna Tipo Tipo de dados JSON
      0 null
      1 cadeia (de caracteres)
      2 number
      3 true/false
      4 matriz
      5 objecto

    Somente as propriedades de primeiro nível são retornadas. A instrução falhará se o texto JSON não estiver formatado corretamente.

  • Quando você chama OPENJSON e especifica um esquema explícito WITH na cláusula, a função retorna uma tabela com o esquema definido na WITH cláusula.

Note

As Keycolunas , Valuee são Type retornadas somente quando você usa OPENJSON com o esquema padrão e não estão disponíveis com um esquema explícito.

Remarks

json_path usado no segundo argumento de OPENJSON ou em with_clause pode começar com a lax palavra-chave ou strict .

  • No lax modo, OPENJSON não gera um erro se o objeto ou valor no caminho especificado não puder ser encontrado. Se o caminho não puder ser encontrado, OPENJSON retornará um conjunto de resultados vazio ou um NULL valor.
  • No strictmodo OPENJSON , retorna um erro se o caminho não puder ser encontrado.

Alguns dos exemplos nesta página especificam explicitamente o modo lax de caminho ou strict. O modo de caminho é opcional. Se você não especificar explicitamente um modo de caminho, lax o modo será o padrão. Para saber mais sobre o modo de caminho e as expressões de caminho, veja Expressões de caminho JSON.

Os nomes das colunas no with_clause são correspondidos com as chaves no texto JSON. Se você especificar o nome [Address.Country]da coluna , ele será correspondido com a chave Address.Country. Se desejar fazer referência a uma chave Country aninhada dentro do objeto Address, será necessário especificar o caminho $.Address.Country no caminho da coluna.

json_path pode conter chaves com caracteres alfanuméricos. Fuja do nome da chave no json_path com aspas duplas se você tiver caracteres especiais nas chaves. Por exemplo, $."my key $1".regularKey."key with . dot" corresponde ao valor 1 no seguinte texto JSON:

{
  "my key $1": {
    "regularKey":{
      "key with . dot": 1
    }
  }
}

Examples

Exemplo 1 - Converter uma matriz JSON em uma tabela temporária

O exemplo a seguir fornece uma lista de identificadores como uma matriz JSON de números. A consulta converte a matriz JSON em uma tabela de identificadores e filtra todos os produtos com as IDs especificadas.

DECLARE @pSearchOptions NVARCHAR(4000) = N'[1,2,3,4]'

SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON product.productTypeID = productTypes.value

Esta consulta é equivalente ao exemplo a seguir. No entanto, no exemplo abaixo, você precisa incorporar números na consulta em vez de passá-los como parâmetros.

SELECT *
FROM products
WHERE product.productTypeID IN (1,2,3,4)

Exemplo 2 - Mesclar propriedades de dois objetos JSON

O exemplo a seguir seleciona uma união de todas as propriedades de dois objetos JSON. Os dois objetos têm uma propriedade de nome duplicado. O exemplo usa o valor da chave para excluir a linha duplicada dos resultados.

DECLARE @json1 NVARCHAR(MAX),@json2 NVARCHAR(MAX)

SET @json1=N'{"name": "John", "surname":"Doe"}'

SET @json2=N'{"name": "John", "age":45}'

SELECT *
FROM OPENJSON(@json1)
UNION ALL
SELECT *
FROM OPENJSON(@json2)
WHERE [key] NOT IN (SELECT [key] FROM OPENJSON(@json1))

Exemplo 3 - Unir linhas com dados JSON armazenados em células da tabela usando CROSS APPLY

No exemplo a seguir, a SalesOrderHeader tabela tem uma SalesReason coluna de texto que contém uma matriz de SalesOrderReasons no formato JSON. Os SalesOrderReasons objetos contêm propriedades como Qualidade e Fabricante. O exemplo cria um relatório que une cada linha de ordem de venda aos motivos de vendas relacionados. O OPENJSON operador expande a matriz JSON de motivos de vendas como se os motivos estivessem armazenados em uma tabela filho separada. Em seguida, o CROSS APPLY operador une cada linha de ordem de venda às linhas retornadas pela função de valor de OPENJSON tabela.

SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)

Tip

Quando você precisa expandir matrizes JSON armazenadas em campos individuais e juntá-las com suas linhas pai, normalmente usa o operador Transact-SQL CROSS APPLY . Para obter mais informações sobre CROSS APPLYo , consulte a cláusula FROM.

A mesma consulta pode ser reescrita usando OPENJSON com um esquema explicitamente definido de linhas para retornar:

SELECT SalesOrderID, OrderDate, value AS Reason  
FROM Sales.SalesOrderHeader  
     CROSS APPLY OPENJSON (SalesReasons) WITH (value NVARCHAR(100) '$')

Neste exemplo, o caminho faz referência a $ cada elemento na matriz. Se quiser converter explicitamente o valor retornado, você pode usar esse tipo de consulta.

Exemplo 4 - Combinar linhas relacionais e elementos JSON com CROSS APPLY

A consulta a seguir combina linhas relacionais e elementos JSON nos resultados mostrados na tabela a seguir.

SELECT store.title, location.street, location.lat, location.long  
FROM store  
CROSS APPLY OPENJSON(store.jsonCol, 'lax $.location')   
     WITH (street VARCHAR(500) ,  postcode VARCHAR(500) '$.postcode' ,  
     lon int '$.geo.longitude', lat int '$.geo.latitude')  
     AS location

Results

title street código postal lon lat
Mercados de Alimentos Inteiros Caminho de Redmond 17991 WA 98052 47.666124 -122.10155
Sears 148 Ave NE WA 98052 47.63024 -122.141246,17

Exemplo 5 - Importar dados JSON para o SQL Server

O exemplo a seguir carrega um objeto JSON inteiro em uma tabela do SQL Server.

DECLARE @json NVARCHAR(max)  = N'{  
  "id" : 2,  
  "firstName": "John",  
  "lastName": "Smith",  
  "isAlive": true,  
  "age": 25,  
  "dateOfBirth": "2015-03-25T12:00:00",  
  "spouse": null  
  }';  

  INSERT INTO Person  
  SELECT *   
  FROM OPENJSON(@json)  
  WITH (id INT,  
        firstName NVARCHAR(50), lastName NVARCHAR(50),   
        isAlive BIT, age INT,  
        dateOfBirth DATETIME, spouse NVARCHAR(50))

Exemplo 6 - Exemplo simples com conteúdo JSON

--simple cross apply example
DECLARE @JSON NVARCHAR(MAX) = N'[
{
"OrderNumber":"SO43659",
"OrderDate":"2011-05-31T00:00:00",
"AccountNumber":"AW29825",
"ItemPrice":2024.9940,
"ItemQuantity":1
},
{
"OrderNumber":"SO43661",
"OrderDate":"2011-06-01T00:00:00",
"AccountNumber":"AW73565",
"ItemPrice":2024.9940,
"ItemQuantity":3
}
]'

SELECT root.[key] AS [Order],TheValues.[key], TheValues.[value]
FROM OPENJSON ( @JSON ) AS root
CROSS APPLY OPENJSON ( root.value) AS TheValues

Conteúdo relacionado

  • Last updated on