OPENJSON (Transact-SQL)
Aplica-se a:
SQL Server 2016 (13.x) e versões posteriores Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Ponto de extremidade de análise de SQL no Microsoft Fabric Warehouse no Microsoft Fabric Banco de dados SQL no Microsoft Fabric
OPENJSON é uma função com valor de tabela que analisa um texto JSON e retorna os objetos e as 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 cláusula FROM de uma instrução Transact-SQL exatamente como é usada qualquer outra tabela, exibição ou função com valor de tabela.
Use OPENJSON para importar dados JSON no SQL Server ou converter dados JSON em formato relacional para um aplicativo ou serviço que não possa consumir JSON diretamente.
Observação
A função OPENJSON está disponível somente 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 função OPENJSON. 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
Sintaxe
OPENJSON( jsonExpression [ , path ] ) [ <with_clause> ]
<with_clause> ::= WITH ( { colName type [ column_path ] [ AS JSON ] } [ ,...n ] )
A função com valor de tabela OPENJSON analisa a jsonExpression fornecida como o primeiro argumento e retorna uma ou mais linhas que contêm dados dos objetos JSON na expressão. jsonExpression pode conter subobjetos aninhados. Se você quiser analisar um subobjeto da jsonExpression, especifique um parâmetro path para o subobjeto JSON.
openjson
Por padrão, a função com valor de tabela OPENJSON retorna três colunas que contém o nome da chave, o valor e o tipo de cada par {key:value} encontrado na jsonExpression. Como alternativa, você pode especificar explicitamente o esquema do conjunto de resultados que o OPENJSON retorna fornecendo with_clause.
with_clause
with_clause contém uma lista de colunas com seus tipos para OPENJSON retornar. Por padrão, OPENJSON faz a correspondência de chaves na jsonExpression com os nomes de coluna na with_clause (nesse caso, a correspondência de chave implica a diferenciação entre maiúsculas e minúsculas). Se um nome de coluna não corresponder a um nome de chave, você poderá fornecer um column_path opcional, que é uma Expressão de demarcador JSON que referencia uma chave dentro da jsonExpression.
Argumentos
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 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);
Resultados:
| chave | value | type |
|---|---|---|
| String_value | John | 1 |
| DoublePrecisionFloatingPoint_value | 45 | 2 |
| DoublePrecisionFloatingPoint_value | 2.3456 | 2 |
| BooleanTrue_value | true | 3 |
| BooleanFalse_value | false | 3 |
| Null_value | NULO | 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 referencia um objeto ou uma matriz na jsonExpression. OPENJSON busca no texto JSON na posição especificada e analisa apenas o fragmento referenciado. Para obter mais informações, confira Expressões de demarcador JSON (SQL Server).
No SQL Server 2017 (14.x) e no Banco de Dados SQL do Azure, você pode fornecer uma variável como o valor de path.
O exemplo a seguir retorna um objeto aninhado ao especificar path:
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"')
Resultados
| Chave | Valor |
|---|---|
| 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
Resultados
| mês | temp | month_id |
|---|---|---|
| Jan | 10 | 0 |
| Fev | 12 | 1 |
| Mar | 15 | 2 |
| Abr | 17 | 3 |
| Mai | 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 função OPENJSON retornar. A 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á popular essa coluna com a propriedade "name" no texto JSON. Você pode substituir esse mapeamento padrão usando o argumento column_path.
tipo
É o tipo de dados da coluna de saída.
Observação
Se você também usar a opção AS JSON, a coluna type precisará 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 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 os demarcadores, confira Expressões de demarcador JSON (SQL Server).
AS 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 precisará ser NVARCHAR(MAX).
Se você não especificar AS JSON para uma coluna, a função retornará um valor escalar (por exemplo, int, cadeia de caracteres, 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á nula no modo incerto ou retornará um erro no modo estrito. Esse comportamento é semelhante ao comportamento da função JSON_VALUE.
Se você especificar AS JSON para uma coluna, a função retornará um fragmento JSON da propriedade JSON especificada no caminho especificado. Se o demarcador representar um valor escalar e a propriedade não puder ser encontrada no demarcador especificado, a função retornará um nula no modo incerto ou retornará um erro no modo estrito. Esse comportamento é semelhante ao comportamento da função JSON_QUERY.
Observação
Se você quiser retornar um fragmento JSON aninhado de uma propriedade JSON, forneça o sinalizador AS JSON. Sem essa opção, se a propriedade não puder ser encontrada, OPENJSON retornará um valor NULL em vez da matriz ou do objeto JSON referenciado ou retornará um erro em tempo de execução no modo estrito.
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
)
Resultados
| Número | Data | Cliente | Quantidade | 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 retornado
As colunas que a função OPENJSON retorna dependem da opção WITH.
Quando você chama OPENJSON com o esquema padrão, ou seja, sem especificar um esquema explícito na cláusula WITH, 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 coluna de chave tem uma ordenação BIN2.
Valor. Um valor nvarchar(max) que contém o valor da propriedade. A coluna de valor herda sua ordenação de jsonExpression.
Type. Um valor inteiro que contém o tipo do valor. A coluna Type é retornada somente quando você usa OPENJSON com o esquema padrão. A coluna de tipo tem um dos seguintes valores:
Valor da coluna Type Tipo de dados JSON 0 nulo 1 string 2 número 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 cláusula WITH, a função retorna uma tabela com o esquema definido na cláusula WITH.
Observação
As colunas Key, Value e Type só serão retornadas quando você usar OPENJSON com o esquema padrão e não estão disponíveis com um esquema explícito.
Comentários
json_path usado no segundo argumento de OPENJSON ou em with_clause pode iniciar com a palavra-chave lax ou strict.
- No modo incerto, OPENJSON não gera um erro quando o objeto ou o valor no demarcador especificado não pode ser encontrado. Se o demarcador não puder ser encontrado, OPENJSON retornará um conjunto de resultados vazio ou um valor NULL.
- No modo estrito, OPENJSON retornará um erro se o demarcador não puder ser encontrado.
Alguns dos exemplos nesta página especificam explicitamente o modo de demarcador, ou seja, incerto ou estrito. O modo de demarcador é opcional. Se você não especificar explicitamente o modo de demarcador, o modo incerto será o padrão. Para obter mais informações sobre o modo de demarcador e as expressões de demarcador, confira Expressões de demarcador JSON (SQL Server).
Os nomes de coluna em with_clause são correspondidos às 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 podem conter chaves com caracteres alfanuméricos. Coloque o nome da chave em json_path entre aspas duplas se houver 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
}
}
}
Exemplos
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 name 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 objetos SalesOrderReasons contêm propriedades como Quality e Manufacturer. O exemplo cria um relatório que une cada linha da ordem de venda aos motivos de vendas relacionados. O operador OPENJSON expande a matriz JSON de motivos de vendas como se os motivos estivessem armazenados em uma tabela filha separada. Em seguida, o operador CROSS APPLY une cada linha da ordem de venda às linhas retornadas pela função com valor de tabela OPENJSON.
SELECT SalesOrderID,OrderDate,value AS Reason
FROM Sales.SalesOrderHeader
CROSS APPLY OPENJSON(SalesReasons)
Dica
Quando é necessário expandir matrizes JSON armazenadas em campos individuais e uni-las às suas linhas pai, normalmente se usa o operador Transact-SQL CROSS APPLY. Para obter mais informações sobre CROSS APPLY, confira FROM (Transact-SQL).
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
Resultados
| título | street | postcode | lon | lat |
|---|---|---|---|---|
| Whole Food Markets | 17991 Redmond Way | 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