SQL Server 2016 - Utilizando o suporte nativo a JSON (JSON_VALUE, JSON_QUERY, OPENJSON, FOR JSON, ISJSON, JSON_MODIFY)

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:

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.

SELECT name, state_desc, recovery_model_desc
FROM sys.databases
FOR JSON AUTO

Tabela original:

JSON gerado:

[
    {
        "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.

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:

{
    "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.

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:

{
    "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.

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.

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:

{
    "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.

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.

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.

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.

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

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.

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.

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

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.

-- 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.

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

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.

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.

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.

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.

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

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.

Dirceu Resende

Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.

WhatsApp Telegram Veja minhas certificações

Comentários (0)

Carregando comentários…

Exportando dados do banco para uma string JSON

Exemplo 1

Exemplo 2

Exemplo 3

Exemplo 4

Exemplo 5

Manipulando dados em strings JSON com JSON_VALUE

Exemplo 1

Exemplo 2

Exemplo 3

Exemplo 4

Manipulando dados em strings JSON com JSON_QUERY

Exemplo 1

Exemplo 2

Exemplo 3

Validando strings JSON com a função ISJSON

Exemplo 1

Exemplo 2

Exemplo 3

Importando JSON para tabela com OPENJSON

Exemplo 1

Exemplo 2

Exemplo 3

Exemplo 4

Exemplo 5

Modificando strings JSON com JSON_MODIFY

Exemplo de uso

Dirceu Resende

Posts relacionados

Comentários (0)