Visualizações: 5.491 views
OlĂ¡ pessoal,
Tudo bem com vocĂªs ?
Neste post vou demonstrar como utilizar o suporte nativo do banco de dados SQL Server ao JSON, padrĂ£o de documento muito utilizado em integrações e Webservices atualmente. Esse recurso estĂ¡ disponĂvel a partir da versĂ£o 2016 do SQL Server e tem como objetivo, permitir a exportaĂ§Ă£o de dados do banco para uma string no formato JSON e a leitura e manipulaĂ§Ă£o de dados no formato JSON.
Caso vocĂª esteja utilizando uma versĂ£o anterior ao SQL Server 2016, ou seja, sem suporte nativo a JSON, mas mesmo assim precisa trabalhar com strings JSON, leia o meu post Lendo strings JSON, importando para o banco e exportando para XML no SQL Server e saiba como fazer isso.
Caso vocĂª precise importar/exportar as strings JSON de/para arquivos texto fisicamente, confira esses dois posts:
- SQL Server – Como importar arquivos de texto para o banco (OLE Automation, CLR, BCP, BULK INSERT, OPENROWSET)
- SQL Server – Como exportar dados do banco para arquivo texto (CLR, OLE, BCP)
Exportando dados do banco para uma string JSON
Visualizar conteĂºdo
Pensando na necessidade dos DBA’s e Desenvolvedores de exportarem dados do banco de dados para strings JSON, a Microsoft criou uma variante da FOR XML para tratar especificamente de JSON, que Ă© a FOR JSON.
Sua sintaxe Ă© muito parecida com a do XML, entĂ£o se vocĂª jĂ¡ sabe manipular XML pelo SQL Server, vocĂª jĂ¡ estarĂ¡ bem familiarizado com esse novo recurso da versĂ£o 2016. Caso vocĂª nĂ£o conheça, veja mais acessando o post SQL Server – Como ler, importar e exportar dados de arquivos XML.
Exemplo 1
Neste primeiro exemplo, vou utilizar o modo padrĂ£o do JSON, que Ă© o auto. Ele simplesmente vai gerar o JSON de acordo com os dados informados, sem fazer nenhuma modificaĂ§Ă£o em sua estrutura.
|
1 2 3 |
SELECT name, state_desc, recovery_model_desc FROM sys.databases FOR JSON AUTO |
Tabela original:
JSON gerado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
[ { "name": "master", "state_desc": "ONLINE", "recovery_model_desc": "SIMPLE" }, { "name": "tempdb", "state_desc": "ONLINE", "recovery_model_desc": "SIMPLE" }, { "name": "model", "state_desc": "ONLINE", "recovery_model_desc": "FULL" }, { "name": "msdb", "state_desc": "ONLINE", "recovery_model_desc": "SIMPLE" }, { "name": "CLR", "state_desc": "ONLINE", "recovery_model_desc": "SIMPLE" }, { "name": "dirceuresende", "state_desc": "ONLINE", "recovery_model_desc": "SIMPLE" } ] |
Exemplo 2
Agora neste exemplo, vou utilizar o parĂ¢metro ROOT para definir um elemento raiz para a minha string JSON e tambĂ©m vou mudar o tĂtulos dos campos.
|
1 2 3 |
SELECT name AS [Database], state_desc AS [Situacao], recovery_model_desc AS [Recovery] FROM sys.databases FOR JSON PATH, ROOT('databases') |
Tabela original:
JSON gerado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
{ "databases": [ { "Database": "master", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database": "tempdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database": "model", "Situacao": "ONLINE", "Recovery": "FULL" }, { "Database": "msdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database": "CLR", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database": "dirceuresende", "Situacao": "ONLINE", "Recovery": "SIMPLE" } ] } |
Exemplo 3
Neste exemplo, vou demonstrar 2 opções para se usar com o JSON. O parĂ¢metro INCLUDE_NULL_VALUES serve para incluir as colunas com valor NULL na string JSON gerada (por padrĂ£o, colunas com valor NULL nĂ£o sĂ£o geradas). O parĂ¢metro WITHOUT_ARRAY_WRAPPER serve para remover os caracteres “[” e “]” da string JSON gerada.
|
1 2 3 4 5 6 7 |
SELECT [name] AS [database.name], state_desc AS [database.state], create_date AS [database.create_date], NULL AS [database.valor_nulo] FROM sys.databases FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER |
Resultado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
{ "database": { "name": "master", "state": "ONLINE", "create_date": "2003-04-08T09:13:36.390", "valor_nulo": null } }, { "database": { "name": "tempdb", "state": "ONLINE", "create_date": "2017-02-12T04:26:38.907", "valor_nulo": null } }, { "database": { "name": "model", "state": "ONLINE", "create_date": "2003-04-08T09:13:36.390", "valor_nulo": null } }, { "database": { "name": "msdb", "state": "ONLINE", "create_date": "2016-04-30T00:46:38.773", "valor_nulo": null } }, { "database": { "name": "CLR", "state": "ONLINE", "create_date": "2017-01-24T19:54:11.247", "valor_nulo": null } }, { "database": { "name": "dirceuresende", "state": "ONLINE", "create_date": "2017-02-08T13:35:32.370", "valor_nulo": null } } |
Exemplo 4
Neste exemplo, vou demonstrar como exportar os dados de uma tabela e armazenar a string JSON gerada em uma variĂ¡vel.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @stringJson VARCHAR(MAX) = ( SELECT TOP 2 [name] AS [database.name], state_desc AS [database.state], recovery_model_desc AS [database.options.recovery] FROM sys.databases FOR JSON AUTO ) PRINT @stringJson |
Resultado:
Exemplo 5
Neste Ăºltima exemplo, vou demonstrar como controlar completamente a estrutura da sua string JSON utilizando o parĂ¢metro PATH.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT TOP 2 [name] AS [database.name], state_desc AS [database.state], recovery_model_desc AS [database.options.recovery], [compatibility_level] AS [database.options.compatibility_level], page_verify_option_desc AS [database.options.page_verify], collation_name AS [database.options.collation], create_date AS [database.create_date], is_read_only AS [database.parameters.read_only], is_auto_shrink_on AS [database.parameters.auto_shrink], is_auto_create_stats_on AS [database.parameters.auto_create_stats], is_read_committed_snapshot_on AS [database.parameters.sessions.read_commited_snapshot], is_ansi_null_default_on AS [database.parameters.sessions.ansi_nulls], is_ansi_warnings_on AS [database.parameters.sessions.ansi_warnings], is_arithabort_on AS [database.parameters.sessions.arithabort], user_access_desc AS [database.user_access] FROM sys.databases FOR JSON PATH, ROOT('databases') |
JSON gerado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
{ "databases": [ { "database": { "name": "master", "state": "ONLINE", "options": { "recovery": "SIMPLE", "compatibility_level": 130, "page_verify": "CHECKSUM", "collation": "Latin1_General_CI_AI" }, "create_date": "2003-04-08T09:13:36.390", "parameters": { "read_only": false, "auto_shrink": false, "auto_create_stats": true, "sessions": { "read_commited_snapshot": false, "ansi_nulls": false, "ansi_warnings": false, "arithabort": false } }, "user_access": "MULTI_USER" } }, { "database": { "name": "tempdb", "state": "ONLINE", "options": { "recovery": "SIMPLE", "compatibility_level": 130, "page_verify": "CHECKSUM", "collation": "Latin1_General_CI_AI" }, "create_date": "2017-02-12T04:26:38.907", "parameters": { "read_only": false, "auto_shrink": false, "auto_create_stats": true, "sessions": { "read_commited_snapshot": false, "ansi_nulls": false, "ansi_warnings": false, "arithabort": false } }, "user_access": "MULTI_USER" } } ] } |
Manipulando dados em strings JSON com JSON_VALUE
Visualizar conteĂºdo
Um outro recurso bacana para manipulaĂ§Ă£o de strings JSON Ă© a funĂ§Ă£o JSON_VALUE, que permite manipular strings JSON e retornar as informações desejadas a partir de uma string JSON.
Exemplo 1
Neste primeiro exemplo, vou demonstrar como extrair informações rapidamente utilizando a funĂ§Ă£o JSON_VALUE.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE @stringJson VARCHAR(MAX) = ' { "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' SELECT JSON_VALUE(@stringJson, '$.info.type') AS [info_type], JSON_VALUE(@stringJson, '$.info.address.town') AS [town], JSON_VALUE(@stringJson, '$.info.address.county') AS [county], JSON_VALUE(@stringJson, '$.info.address.country') AS [country], -- para retornar o array, utilize JSON_QUERY JSON_VALUE(@stringJson, '$.info.tags') AS [tags], -- retornando os dados do array JSON_VALUE(@stringJson, '$.info.tags[0]') AS [tags1], JSON_VALUE(@stringJson, '$.info.tags[1]') AS [tags2], JSON_VALUE(@stringJson, '$.info.tags[2]') AS [tags3], -- nĂ£o existe = NULL JSON_VALUE(@stringJson, '$.type') AS [type] |
Resultado:
Exemplo 2
Neste exemplo, vou demonstrar como ler dados de strings JSON que possuem dados multivalorados (arrays) e a leitura serĂ¡ feita utilizando Ăndices de arrays.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
DECLARE @stringJson VARCHAR(MAX) = ' { "databases": [ { "Database Name": "master", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "tempdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "model", "Situacao": "ONLINE", "Recovery": "FULL" }, { "Database Name": "msdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "CLR", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "dirceuresende", "Situacao": "ONLINE", "Recovery": "SIMPLE" } ] }' SELECT JSON_VALUE(@stringJson, '$.databases[0]."Database Name"'), JSON_VALUE(@stringJson, '$.databases[0].Situacao'), JSON_VALUE(@stringJson, '$.databases[1]."Database Name"'), JSON_VALUE(@stringJson, '$.databases[1].Situacao'), JSON_VALUE(@stringJson, '$.databases[2]."Database Name"'), JSON_VALUE(@stringJson, '$.databases[2].Situacao'), JSON_VALUE(@stringJson, '$.databases[5]."Database Name"'), JSON_VALUE(@stringJson, '$.databases[5].Situacao') |
Resultado:
Exemplo 3
Neste exemplo, vou demonstrar como utilizar o JSON_VALUE utilizando uma string embutida no prĂ³prio comando.
|
1 2 3 |
SELECT JSON_VALUE('{"Primeiro Nome":"Dirceu", "Ultimo Nome": "Resende"}', '$."Primeiro Nome"'), JSON_VALUE('{"Primeiro Nome":"Dirceu", "Ultimo Nome": "Resende"}', '$."Ultimo Nome"') |
Resultado:
Exemplo 4
Por fim, neste exemplo vou demonstrar como utilizar a funĂ§Ă£o JSON_VALUE como coluna computada, onde insiro uma string JSON na tabela, e os dados sĂ£o calculados automaticamente pra mim.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
IF (OBJECT_ID('tempdb..#Teste') IS NOT NULL) DROP TABLE #Teste CREATE TABLE #Teste ( Id INT IDENTITY(1 ,1), stringJson VARCHAR(MAX), Nome AS JSON_VALUE(stringJson, '$."Primeiro Nome"'), Ultimo_Nome AS JSON_VALUE(stringJson, '$."Ultimo Nome"'), ) INSERT #Teste (stringJson) VALUES('{"Primeiro Nome":"Dirceu", "Ultimo Nome": "Resende"}') SELECT * FROM #Teste |
Resultado:
Manipulando dados em strings JSON com JSON_QUERY
Visualizar conteĂºdo
ApĂ³s aprender como utilizar o JSON_VALUE para trabalhar com informações escalares, vou demonstrar como utilizar a funĂ§Ă£o JSON_QUERY para retornar objetos e arrays de valores JSON.
Exemplo 1
Neste primeiro exemplo, vou demonstrar algumas utilizações bĂ¡sicas da funĂ§Ă£o JSON_QUERY
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
DECLARE @stringJson VARCHAR(MAX) = ' { "info": { "type": 1, "address": { "town": "Bristol", "county": "Avon", "country": "England" }, "tags": ["Sport", "Water polo"] }, "type": "Basic" } ' SELECT -- Retorna o objeto JSON completo JSON_QUERY(@stringJson, '$') AS JSON_Completo SELECT -- Retorna o objeto JSON "info" JSON_QUERY(@stringJson, '$.info') AS [Info] SELECT -- Retorna o objeto JSON "address" JSON_QUERY(@stringJson, '$.info.address') AS [Address] SELECT -- Retorna NULL, pois JSON_QUERY sĂ³ funciona com objetos -- Para retornar valores escalares, use a JSON_VALUE JSON_QUERY(@stringJson, '$.info.type') AS [Type] SELECT -- Retorna o array de valores da propriedade "tags JSON_QUERY(@stringJson, '$.info.tags') AS [Tags] |
Resultado:
Exemplo 2
Neste exemplo, vou demonstrar como retornar arrays utilizando Ăndices-N a partir de uma string JSON.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
DECLARE @stringJson VARCHAR(MAX) = ' { "databases": [ { "Database Name": "master", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "tempdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "model", "Situacao": "ONLINE", "Recovery": "FULL" }, { "Database Name": "msdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "CLR", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "dirceuresende", "Situacao": "ONLINE", "Recovery": "SIMPLE" } ] }' SELECT JSON_QUERY(@stringJson, '$.databases[0]') SELECT JSON_QUERY(@stringJson, '$.databases[1]') SELECT JSON_QUERY(@stringJson, '$.databases[2]') |
Resultado:
Exemplo 3
Neste exemplo, vou fazer algumas comparações entre o JSON_QUERY e JSON_VALUE, para demonstrar a diferença das 2 funções.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 |
DECLARE @stringJson VARCHAR(MAX) = ' { "databases": [ { "database": { "name": "master", "teste": ["Teste 1", "Teste 2", "Teste 3"], "state": "ONLINE", "options": { "recovery": "SIMPLE", "compatibility_level": 130, "page_verify": "CHECKSUM", "collation": "Latin1_General_CI_AI" }, "create_date": "2003-04-08T09:13:36.390", "parameters": { "read_only": false, "auto_shrink": false, "auto_create_stats": true, "sessions": { "read_commited_snapshot": false, "ansi_nulls": false, "ansi_warnings": false, "arithabort": false } }, "user_access": "MULTI_USER" } }, { "database": { "name": "tempdb", "teste": ["Teste 1", "Teste 2", "Teste 3"], "state": "ONLINE", "options": { "recovery": "SIMPLE", "compatibility_level": 130, "page_verify": "CHECKSUM", "collation": "Latin1_General_CI_AI" }, "create_date": "2017-02-12T04:26:38.907", "parameters": { "read_only": false, "auto_shrink": false, "auto_create_stats": true, "sessions": { "read_commited_snapshot": false, "ansi_nulls": false, "ansi_warnings": false, "arithabort": false } }, "user_access": "MULTI_USER" } } ] }' -- Quando usar o JSON_QUERY para retornar objetos SELECT JSON_QUERY(@stringJson, '$') AS [JSON_Query], JSON_VALUE(@stringJson, '$') AS [JSON_Value] SELECT JSON_QUERY(@stringJson, '$.databases[0]') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0]') AS [JSON_Value] SELECT JSON_QUERY(@stringJson, '$.databases[0].database') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0].database') AS [JSON_Value] SELECT JSON_QUERY(@stringJson, '$.databases[0].database.teste') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0].database.teste') AS [JSON_Value] SELECT JSON_QUERY(@stringJson, '$.databases[0].database.options') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0].database.options') AS [JSON_Value] -- Quando usar o JSON_VALUE para retornar valores SELECT JSON_QUERY(@stringJson, '$.databases[0].database.teste[0]') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0].database.teste[0]') AS [JSON_Value], JSON_QUERY(@stringJson, '$.databases[0].database.teste[1]') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0].database.teste[1]') AS [JSON_Value] SELECT JSON_QUERY(@stringJson, '$.databases[0].database.name') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0].database.name') AS [JSON_Value] SELECT JSON_QUERY(@stringJson, '$.databases[0].database.options.recovery') AS [JSON_Query], JSON_VALUE(@stringJson, '$.databases[0].database.options.recovery') AS [JSON_Value] |
Resultado:
Validando strings JSON com a funĂ§Ă£o ISJSON
Visualizar conteĂºdo
Uma funĂ§Ă£o muito Ăºtil para se trabalhar com JSON Ă© a ISJSON, que permite verificar se uma string Ă© uma string JSON Ă© estĂ¡ em conformidade com os padrões de JSON reconhecidos e que podem ser lidos pelo SQL Server.
Vou demonstrar alguns exemplos para vocĂªs entenderem facilmente como se utilizar essa funĂ§Ă£o.
Exemplo 1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
DECLARE @stringJson VARCHAR(MAX) = ' { "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo"] }, "type":"Basic" }' SELECT ISJSON(@stringJson) |
Resultado:
1 (JSON vĂ¡lido)
Exemplo 2
Neste exemplo, vou usar exatamente o mesmo JSON do exemplo anterior, mas vou remover um bracket “]” da string JSON para que ele nĂ£o seja mais vĂ¡lido.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- Removi o caractere "]" da linha 12 DECLARE @stringJson VARCHAR(MAX) = ' { "info":{ "type":1, "address":{ "town":"Bristol", "county":"Avon", "country":"England" }, "tags":["Sport", "Water polo" }, "type":"Basic" }' SELECT ISJSON(@stringJson) |
Resultado:
0 (JSON invĂ¡lido)
Exemplo 3
Por fim, vou demonstrar alguns exemplos rĂ¡pidos de validaĂ§Ă£o de JSON.
|
1 2 3 4 5 6 |
SELECT ISJSON('Teste') AS Invalido1, ISJSON('') AS Invalido2, ISJSON(NULL) AS Retorna_Null, ISJSON('{"Primeiro Nome":"Dirceu", "Ultimo Nome": "Resende"}') AS Valido1, ISJSON('"Primeiro Nome":"Dirceu", "Ultimo Nome": "Resende"') AS Invalido3 |
Resultado:
Importando JSON para tabela com OPENJSON
Visualizar conteĂºdo
Utilizando a funĂ§Ă£o OPENJSON, podemos facilmente importar os dados de uma string JSON para uma tabela do SQL Server. Reparem que neste exemplo, o JSON possui um elemento raiz (databases). Por isso, vou utilizar o seletor ‘$.databases’ na funĂ§Ă£o OPENJSON para facilitar a navegaĂ§Ă£o nos elementos sem precisar ter que sempre informar o elemento raiz.
Exemplo 1
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 |
DECLARE @stringJson VARCHAR(MAX) = ' { "databases": [ { "Database Name": "master", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "tempdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "model", "Situacao": "ONLINE", "Recovery": "FULL" }, { "Database Name": "msdb", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "CLR", "Situacao": "ONLINE", "Recovery": "SIMPLE" }, { "Database Name": "dirceuresende", "Situacao": "ONLINE", "Recovery": "SIMPLE" } ] }' SELECT * FROM OPENJSON(@stringJson, '$.databases') WITH ( Ds_Database NVARCHAR(100) '$."Database Name"', Ds_Situacao NVARCHAR(40) '$.Situacao', Ds_Recovery NVARCHAR(20) '$.Recovery' ) AS JsonImportado |
Resultado:
Exemplo 2
Neste exemplo, vou demonstrar como importar outro JSON, desta vez sem elemento raiz e vou converter a data para DATETIME.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
DECLARE @stringJson VARCHAR(MAX) = ' [ {"Pedido": 1, "Dt_Pedido": "18/02/2017", "Cd_Cliente": 25, "Qtde_Itens": 5, "Cd_Produto": 5, "Vl_Unitario": 154.54}, {"Pedido": 4, "Dt_Pedido": "14/02/2017", "Cd_Cliente": 7, "Qtde_Itens": 6, "Cd_Produto": 4, "Vl_Unitario": 59.99}, {"Pedido": 6, "Dt_Pedido": "12/02/2017", "Cd_Cliente": 9, "Qtde_Itens": 8, "Cd_Produto": 2, "Vl_Unitario": 150}, {"Pedido": 8, "Dt_Pedido": "12/02/2017", "Cd_Cliente": 5, "Qtde_Itens": 1, "Cd_Produto": 8, "Vl_Unitario": 287.00} ]' SELECT JsonImportado.Nr_Pedido , CONVERT(DATETIME, JsonImportado.Dt_Pedido, 103) AS Dt_Pedido, JsonImportado.Cd_Cliente , JsonImportado.Qtde_Itens , JsonImportado.Cd_Produto , JsonImportado.Vl_Unitario FROM OPENJSON(@stringJson) WITH ( Nr_Pedido INT '$.Pedido', Dt_Pedido NVARCHAR(10) '$.Dt_Pedido', Cd_Cliente INT '$.Cd_Cliente', Qtde_Itens INT '$.Qtde_Itens', Cd_Produto INT '$.Cd_Produto', Vl_Unitario FLOAT '$.Vl_Unitario' ) AS JsonImportado |
Resultado:
Exemplo 3
Neste exemplo, vou demonstrar como importar dados de uma string JSON multinĂvel para tabela.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
DECLARE @stringJson VARCHAR(MAX) = ' { "databases": [ { "database": { "name": "master", "state": "ONLINE", "options": { "recovery": "SIMPLE", "compatibility_level": 130, "page_verify": "CHECKSUM", "collation": "Latin1_General_CI_AI" }, "create_date": "2003-04-08T09:13:36.390", "parameters": { "read_only": false, "auto_shrink": false, "auto_create_stats": true, "sessions": { "read_commited_snapshot": false, "ansi_nulls": false, "ansi_warnings": false, "arithabort": false } }, "user_access": "MULTI_USER" } }, { "database": { "name": "tempdb", "state": "ONLINE", "options": { "recovery": "SIMPLE", "compatibility_level": 130, "page_verify": "CHECKSUM", "collation": "Latin1_General_CI_AI" }, "create_date": "2017-02-12T04:26:38.907", "parameters": { "read_only": false, "auto_shrink": false, "auto_create_stats": true, "sessions": { "read_commited_snapshot": false, "ansi_nulls": false, "ansi_warnings": false, "arithabort": false } }, "user_access": "MULTI_USER" } } ] }' SELECT * FROM OPENJSON(@stringJson, '$.databases') WITH ( [Database_Name] NVARCHAR(100) '$.database.name', [Database_State] NVARCHAR(100) '$.database.state', [Recovery] NVARCHAR(40) '$.database.options.recovery', [Compatibility_Level] INT '$.database.options.compatibility_level', [Page_Verify] NVARCHAR(20) '$.database.options.page_verify', [Collation] NVARCHAR(50) '$.database.options.collation', -- InformaĂ§Ă£o booleada em variĂ¡vel BIT [Read_Only] BIT '$.database.parameters.read_only', [Read_Commited_Snapshot] BIT '$.database.parameters.sessions.read_commited_snapshot', -- InformaĂ§Ă£o booleada em variĂ¡vel NVARCHAR [Ansi_Nulls] NVARCHAR(50) '$.database.parameters.sessions.ansi_nulls', [Ansi_Warnings] NVARCHAR(50) '$.database.parameters.sessions.ansi_warnings', [Arithabort] NVARCHAR(50) '$.database.parameters.sessions.arithabort', [User_Acess] NVARCHAR(40) '$.database.user_access' ) AS JsonImportado |
Resultado:
Exemplo 4
Neste exemplo, vou demonstrar como importar dados de uma string JSON multinĂvel, retornando apenas a listagem, para tabela. Nesse cenĂ¡rio, terei que utilizar CROSS APPLY entre os nĂ³s do JSON para percorrĂª-lo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
DECLARE @json NVARCHAR(MAX) = ' [ { "sendSmsMultiResponse":{ "testToken": "ok", "sendSmsResponseList":[ { "statusCode":"00", "statusDescription":"Ok", "detailCode":"000", "detailDescription":"Message Sent" }, { "statusCode":"01", "statusDescription":"Ok", "detailCode":"000", "detailDescription":"Message Sent" }, { "statusCode":"02", "statusDescription":"Ok", "detailCode":"000", "detailDescription":"Message Delivered" }, { "statusCode":"03", "statusDescription":"Error", "detailCode":"000", "detailDescription":"Failure Sending Message" }, { "statusCode":"04", "statusDescription":"Ok", "detailCode":"000", "detailDescription":"Message Sent" } ] } } ]' SELECT lista.testToken, items.* FROM OPENJSON(@json) WITH ( sendSmsMultiResponse NVARCHAR(MAX) AS JSON ) AS retorno CROSS APPLY OPENJSON(retorno.sendSmsMultiResponse) WITH ( testToken NVARCHAR(100), sendSmsResponseList NVARCHAR(MAX) AS JSON ) AS lista CROSS APPLY OPENJSON(lista.sendSmsResponseList) WITH ( statusCode NVARCHAR(10), statusDescription NVARCHAR(50), detailCode NVARCHAR(10), detailDescription NVARCHAR(50) ) AS items |
Resultado:
Exemplo 5
Neste Ăºltimo exemplo, vou demonstrar novamente como importar dados de uma string JSON multinĂvel, retornando apenas a listagem, para tabela. Nesse cenĂ¡rio, terei que utilizar CROSS APPLY entre os nĂ³s do JSON para percorrĂª-lo.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
DECLARE @json NVARCHAR(MAX) = ' [ { "structures":[ { "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F", "IdProject":"97A76363-095D-4FAB-940E-9ED2722DBC47", "Name":"Test Structure", "BaseStructure":"Base Structure", "DatabaseSchema":"dbo", "properties":[ { "IdProperty":"618DC40B-4D04-4BF8-B1E6-12E13DDE86F4", "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F", "Name":"Test Property 2", "DataType":1, "Precision":2, "Scale":0, "IsNullable":false, "ObjectName":"Test Object", "DefaultType":0, "DefaultValue":"T" }, { "IdProperty":"FFF433EC-0BB5-41CD-8A71-B5F09B97C5FC", "IdStructure":"CB0466F9-662F-412B-956A-7D164B5D358F", "Name":"Test Property 1", "DataType":2, "Precision":4, "Scale":0, "IsNullable":true, "ObjectName":"Test Object 2", "DefaultType":1, "DefaultValue":"F" } ] } ] } ]' SELECT Structures.IdStructure, Structures.Name, Structures.BaseStructure, Structures.DatabaseSchema, Properties.IdProperty, Properties.NamePreoperty, Properties.DataType, Properties.Precision, Properties.Scale, Properties.IsNullable, Properties.ObjectName, Properties.DefaultType, Properties.DefaultValue FROM OPENJSON(@json) WITH ( structures NVARCHAR(MAX) AS JSON ) AS Projects CROSS APPLY OPENJSON(Projects.structures) WITH ( IdStructure UNIQUEIDENTIFIER, [Name] NVARCHAR(100), BaseStructure NVARCHAR(100), DatabaseSchema sysname, properties NVARCHAR(MAX) AS JSON ) AS Structures CROSS APPLY OPENJSON(Structures.properties) WITH ( IdProperty UNIQUEIDENTIFIER, NamePreoperty NVARCHAR(100) '$.Name', DataType INT, [Precision] INT, [Scale] INT, IsNullable BIT, ObjectName NVARCHAR(100), DefaultType INT, DefaultValue NVARCHAR(100) ) AS Properties |
Resultado:
Modificando strings JSON com JSON_MODIFY
Visualizar conteĂºdo
Utilizando a funĂ§Ă£o JSON_MODIFY, pode-se facilmente alterar os dados de uma string JSON e trabalhar posteriormente com esses dados de acordo com a sua necessidade.
Exemplo de uso
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
DECLARE @info NVARCHAR(MAX) = '{"nome":"Mike","habilidades":["C#","SQL"],"idade":29}' PRINT '-- JSON Original' PRINT @info PRINT '' -- Altera o nome de "Mike" para "Dirceu" SET @info=JSON_MODIFY(@info,'$.nome','Dirceu') PRINT '-- Altera o nome de "Mike" para "Dirceu"' PRINT @info PRINT '' -- Insere uma nova propriedade chamada "sobrenome" com o valor "Resende" SET @info=JSON_MODIFY(@info,'$.sobrenome','Resende') PRINT '-- Insere uma nova propriedade chamada "sobrenome" com o valor "Resende"' PRINT @info PRINT '' -- Adiciona um novo valor no array "habilidades" SET @info=JSON_MODIFY(@info,'append $.habilidades','Azure') PRINT '-- Adiciona um novo valor no array "habilidades"' PRINT @info PRINT '' -- Tenta redefiner os valores do array "habilidades" SET @info=JSON_MODIFY(@info,'$.habilidades', '["C++","T-SQL","PHP", "CSS"]') PRINT '-- Tenta redefiner os valores do array "habilidades", mas tem problemas com os caracteres nĂ£o-escapados' PRINT @info PRINT '' -- Redefine os valores do array "habilidades" SET @info=JSON_MODIFY(@info,'$.habilidades', JSON_QUERY('["C++","T-SQL","PHP", "CSS"]')) PRINT '-- Redefine os valores do array "habilidades" utilizando JSON_QUERY para escapar os caracteres corretamente' PRINT @info PRINT '' -- Renomeando a propriedade "nome" para "Primeiro Nome" (Precisa excluir e criar uma nova) SET @info = JSON_MODIFY(@info, '$."Primeiro Nome"', JSON_VALUE(@info, '$.nome')) SET @info = JSON_MODIFY(@info, '$.nome', NULL) PRINT '-- Renomeando a propriedade "nome" para "Primeiro Nome"' PRINT @info PRINT '' -- Remove a propriedade ""Primeiro Nome"" SET @info=JSON_MODIFY(@info,'$."Primeiro Nome"',NULL) PRINT '-- Remove a propriedade "primeiroNome"' PRINT @info PRINT '' -- Incrementa o valor da propriedade "Idade" de 29 para 30 SET @info = JSON_MODIFY(@info, '$.idade', CAST(JSON_VALUE(@info, '$.idade') AS INT) + 1) PRINT '-- Incrementa o valor da propriedade "Idade" de 29 para 30' PRINT @info PRINT '' -- Realiza mais de uma atualizaĂ§Ă£o no mesmo comando SET @info = JSON_MODIFY(JSON_MODIFY(JSON_MODIFY(@info, '$.idade', 28), '$."Primeiro Nome"', 'Resende'), '$.sobrenome', 'Dirceu') PRINT '-- Realiza mais de uma atualizaĂ§Ă£o no mesmo comando' PRINT @info PRINT '' |
Resultado:
É isso aĂ, pessoal!
Espero que tenham gostado desse post.
AtĂ© a prĂ³xima.
Portuguese 
English
Bacana Dirceu, parabéns!
Obrigado pelo feedback, Ronaldo. Qualquer dĂºvida, Ă© sĂ³ falar.
NĂ£o ficou claro para mim como se armazena um JSON dentro de uma tabela. Existe um tipo de dados para JSON nativamente? VocĂª tem operadores para este tipo de dados para utilizar numa clĂ¡usula WHERE? É possĂvel indexar um JSON?
Telles,
Bom dia.
O JSON Ă© armazenado como string (VARCHAR ou NVARCHAR), diferente do XML, que possui um tipo de dado especĂfico para ele. Os operadores para trabalhar com JSON em um WHERE sĂ£o os mesmos do SELECT, como ISJON, JSON_VALUE, etc.. Assim como uma string qualquer, vocĂª pode indexar as strings JSON normalmente.
Vou editar o post depois para adicionar esses exemplos. đŸ™‚
Entendi, Ă© como o PostgreSQL fazia na primeira implementaĂ§Ă£o do JSON. Mas o que passamos a fazer depois disso Ă© indexar rĂ³tulos internos do JSON, EX: recovery_model_desc = ‘COMPLEX’;
O SQL Server faz isso?