OPENJSON (Transact-SQL)

Aplica-se a: SQL Server 2016 (13.x) e versões posteriores do Banco de Dados SQL doAzure Azure Instância Gerenciada de SQLdo Azure Synapse Analytics ponto deextremidade de análise de SQL no Microsoft Fabric Warehouse no Banco de Dados SQLdo Microsoft Fabricno Microsoft Fabric

A OPENJSON função com valor de tabela analisa o 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 em um documento JSON. Você pode especificar explicitamente as colunas no conjunto de linhas e os demarcadores de propriedades do JSON usados para popular as colunas. Como OPENJSON retorna um conjunto de linhas, você pode usar OPENJSON na FROM cláusula de uma instrução Transact-SQL assim como 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 só está disponível no 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.

É possível verificar o nível de compatibilidade na exibição sys.databases ou nas propriedades do banco de dados. É possível alterar o nível de compatibilidade de um banco de dados usando o seguinte comando:

ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130

Convenções de sintaxe de Transact-SQL

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 o jsonExpression fornecido como o primeiro argumento e retorna uma ou mais linhas que contêm dados dos objetos JSON na expressão. jsonExpression pode conter sub-objetos aninhados. Se você quiser analisar um subprojeto de dentro de jsonExpression, poderá especificar um parâmetro de caminho para o subprocurso JSON.

openjson

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

O with_clause contém uma lista de colunas com seus tipos para OPENJSON retornar. Por padrão, OPENJSON a correspondência de chaves em jsonExpression com os nomes de coluna em with_clause (nesse caso, as chaves de correspondência implicam que ela 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ão de caminho JSON que faz referência a uma chave dentro da jsonExpression.

Arguments

jsonExpression

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

OPENJSON itera sobre os elementos da matriz ou das propriedades do objeto na expressão de JSON e retorna uma linha para cada elemento ou propriedade. O exemplo a seguir retorna cada propriedade do objeto fornecida 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. (Não há suporte para isso no SQL Server 2016 (13.x) e nas 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 demarcador com as propriedades da expressão JSON diferencia maiúsculas de minúsculas e não reconhece ordenação (ou seja, é uma comparação BIN2).

Identidade do elemento da matriz

A função OPENJSON no pool de SQL sem servidor no Azure Synapse Analytics pode gerar automaticamente a identidade de cada linha 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 baseado em 0 exclusivo para cada elemento na matriz JSON que a função analisar. O valor de identidade representa a posição/índice do elemento de 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 apenas no pool de SQL sem servidor no Synapse Analytics.

with_clause

Define explicitamente o esquema de saída para a OPENJSON função a ser retornada. 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 da coluna de saída.

Note

Se você também usar a opção AS JSON , o tipo de dados de coluna deverá 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 de caminho 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 demarcador com as propriedades da expressão JSON diferencia maiúsculas de minúsculas e não reconhece ordenação (ou seja, é uma comparação BIN2).

Para obter mais informações sobre caminhos, consulte Expressões de Caminho JSON.

COMO JSON

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

• Se você não especificar AS JSON 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 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 referenciado 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	{"Number":"SO43659","Date":"2011-05-31T00:00:00"}
SO43661	2011-06-01T00:00:00	AW73565	3	{"Number":"SO43661","Date":"2011-06-01T00:00:00"}

Valor de retorno

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

• Quando você chama OPENJSON com o esquema padrão , ou seja, quando você não especifica um esquema explícito na cláusula - a WITH 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 uma ordenação BIN2.

  • Value. Um valor nvarchar(MAX) que contém o valor da propriedade. A value coluna herda sua ordenação 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 Type	Tipo de dados JSON
    0	nulo
    1	cadeia
    2	number
    3	true/false
    4	matriz
    5	objeto

  Somente 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 na WITH cláusula, a função retorna uma tabela com o esquema que você definiu na WITH cláusula.

Note

As Keycolunas , Valuee Type , somente são retornadas 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 palavra-chave ou lax a strict palavra-chave.

• No lax modo, OPENJSON não gerará um erro se o objeto ou o 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 , retornará 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 demarcador é opcional. Se você não especificar explicitamente um modo de caminho, lax o modo será o padrão. Para obter mais informações sobre o modo de caminho e expressões de caminho, consulte Expressões de caminho JSON.

Os nomes de coluna em with_clause são correspondidos com chaves no texto JSON. Se você especificar o nome da coluna [Address.Country], ele será correspondido com a chave Address.Country. Se você quiser referenciar uma chave aninhada Country dentro do objeto Address, especifique o demarcador $.Address.Country no demarcador da coluna.

json_path pode conter chaves com caracteres alfanuméricos. Escape do nome da chave em 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 inserir 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 duplicada. 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 os dados JSON armazenados em células de tabela usando CROSS APPLY

No exemplo a seguir, a tabela SalesOrderHeader tem uma coluna de texto SalesReason 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 da ordem de venda aos motivos de vendas relacionados. O OPENJSON operador expande a matriz JSON de motivos de vendas como se os motivos fossem armazenados em uma tabela filho separada. Em seguida, o CROSS APPLY operador une cada linha de pedido de venda às linhas retornadas pela OPENJSON função com valor de 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 você usa o operador Transact-SQL CROSS APPLY . Para obter mais informações sobre CROSS APPLY, consulte a cláusula FROM.

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

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

Neste exemplo, o demarcador $ referencia cada elemento da matriz. Se você quiser converter explicitamente o valor retornado, use esse tipo de consulta.

Exemplo 4: combinar linhas relacionais e elementos JSON com CROSS APPLY

A consulta a seguir combina as linhas relacionais e os 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	postcode	lon	lat
Mercados de Alimentos Inteiros	Redmond Way 17991	WA 98052	47.666124	-122.10155
Sears	148th Ave NE	WA 98052	47.63024	-122.141246,17

Exemplo 5: importar dados JSON no 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

• Expressões de caminho JSON
• Analisar e transformar dados JSON com OPENJSON
• Usar OPENJSON com o esquema padrão
• Usar OPENJSON com um esquema explícito