A função JSON_QUERY () para extrair objetos de dados JSON

A função JSON_QUERY () para extrair objetos de dados JSON

A função JSON_QUERY () para extrair objetos de dados JSON 1


Neste artigo, exploraremos as funções JSON_QUERY () no SQL Server para extrair objetos JSON e matriz do diretório
    Dados JSON.

Visão geral do JSON

JSON (JavaScript Object Notation) é um formato acessível para representar dados de maneira estruturada. Isso consiste de
    dados leves para troca de dados. Se você estiver familiarizado com o Amazon Web Service, DynamoDB, MongoDB, Couchbase
    bancos de dados, você deve estar familiarizado com os documentos JSON. Esses bancos de dados NoSQL usam principalmente JSON estruturado
    dados. Torna-se necessário que os administradores de banco de dados SQL também entendam JSON e usem a função SQL Server em
    em conjunto com JSON.

  • Consiste em pares de valores-chave.

  • É uma ponte entre o No-SQL e os bancos de dados relacionais. Você pode consultar o artigo SQL
                Funções JSON do servidor: uma ponte entre o NoSQL e os mundos relacionais para obter mais detalhes

  • Cada chave deve ser colocada entre aspas duplas

  • Podemos ter uma string, objeto, matriz, formato de dados booleano ou numérico em um valor

  • Cada chave deve usar dois pontos (:) para segregar dos valores. Por exemplo “Nome”: “Rajendra”

Antes de prosseguirmos, darei uma pequena visão geral do objeto e da matriz JSON.

  • Objeto JSON:

    No JSON, cada objeto é colocado entre colchetes ({}). No exemplo abaixo, temos uma chave (Nome),
        e seu valor é um objeto JSON (JSON aninhado)

  • Matriz JSON

    É uma lista ordenada de valores para armazenar vários valores. Usamos colchetes para representar uma matriz nos dados JSON. No exemplo a seguir, temos uma matriz de funcionários que possui registros dos funcionários.

O SQL Server fornece as seguintes funções JSON para trabalhar com dados JSON:

  • ISJSON (): podemos verificar JSON válido usando esta função
  • JSON_VALUE (): Extrai um valor escalar dos dados JSON
  • JSON_MODIFY (): Ele modifica valores nos dados JSON. Você deve passar por Modificando dados JSON usando JSON_MODIFY () no SQL Server para esta função
  • JSON_QUERY: Extrai uma matriz ou string de JSON no SQL Server

Podemos visualizar essas funções na imagem a seguir dos documentos da Microsoft.

Visão geral do suporte JSON interno

Exploramos as funções JSON_VALUE () e JSON_MODIFY () nos meus artigos anteriores. Você pode consultar o JSON para esses artigos. Neste artigo, estamos explorando a função JSON_QUERY () usando vários exemplos.

Sintaxe de JSON_QUERY ()

JSON_QUERY (expressão,[Path Mode] JSON_path)

  • Expressão: É uma sequência JSON ou uma variável que contém dados JSON
  • JSON_Path: É o caminho do objeto ou de uma matriz de onde queremos recuperar valores
  • Modo de caminho: Controla a saída de uma função JSON_QUERY () no caso de uma sequência JSON inválida usando os argumentos LAX e Strict

Exemplo 1: Obter o objeto JSON de uma sequência JSON

Neste exemplo, precisamos recuperar o primeiro objeto JSON do diretório [employees] chave.

  • Uma variável @data contém uma matriz para o “Funcionários” chave

  • Podemos notar que a matriz está entre colchetes

  • A matriz JSON segue a indexação baseada em zero. Para recuperar o primeiro registro, usamos empregados[0] argumento

  • Da mesma forma, podemos acessar o segundo registro usando o empregados[1] argumento

Ele recupera o primeiro objeto JSON e fornece a seguinte saída.

Saída JSON_QUERY

Podemos recuperar o documento JSON inteiro removendo o segundo argumento. É um argumento opcional, por isso não
    obtenha algum erro. Também podemos usar o argumento ‘$’ para obter toda a cadeia JSON.

Você pode ver o documento inteiro como parte da função JSON_QUERY ().

Função JSON_QUERY () para dados JSON

Como você sabe, não podemos usar a função JSON_VALUE () para recuperar um objeto ou matriz. Recupera um valor escalar
    de uma sequência JSON. Você obtém um valor NULL na saída se substituirmos a função JSON_MODIFY () por JSON_VALUE ().

Retorna um valor NULL na saída, como mostrado abaixo.

Valores NULL na função JSON_QUERY ()

Exemplo 2: recuperar um valor escalar usando a função JSON_QUERY ()

Como você sabe, usamos a função JSON_VALUE () para recuperar um valor escalar. Se tentarmos recuperar o valor escalar usando
    JSON_QUERY (), vamos ver a saída.

Também fornece valor NULL na saída. Se tentarmos obter um valor escalar da função JSON_QUERY ().

Valor escalar

Por padrão, a função JSON_QUERY () usa um modo de caminho padrão relaxado. Nesse modo, o SQL Server não
    gerar um erro no caso de qualquer chave ou valor inválido. Convém gerar uma mensagem de erro em vez de obter uma
    Valor NULL na saída. Podemos usar rigoroso modo para receber a mensagem de erro.

Modo estrito

Exemplo 3: recuperar JSON pelo símbolo $ usando a função JSON_QUERY ()

No exemplo a seguir, temos uma string JSON que contém pares de valores-chave, uma matriz JSON e um objeto JSON.

Primeiro, usamos o argumento $ no segundo parâmetro e obtemos a string JSON completa, como mostrado abaixo.

Na saída, também podemos notar a mensagem – 1 linha afetada. Ele trata toda a cadeia JSON como uma única linha no
    Servidor SQL.

Recuperar JSON pelo símbolo $

Agora, queremos recuperar a matriz Employees. Nesse caso, podemos especificar a matriz com a qual queremos recuperar
    o símbolo $. Abaixo, especificamos como $ .Employees.

Na saída, obtemos a matriz dos funcionários sem a chave. Podemos notar que a matriz começa e termina com um quadrado
    suporte.

Matriz JSON sem chave raiz

Além deste exemplo, precisamos recuperar a segunda linha (objeto JSON)

. Como já sabemos, o JSON usa um
    processo de indexação baseado em zero e podemos especificar o segundo argumento $ .Employees[1].

Podemos filtrar ainda mais o JSON e obter o objeto JSON do endereço do cliente. Aqui, podemos especificar mais argumentos como
    $ .Empregados[1].Endereço.

Ver saída

Consulta JSON no banco de dados de exemplo AdventureWorks

Podemos usar a função JSON_Query () no banco de dados de exemplo AdventureWorks. Para esta demonstração, você pode usar o abaixo
    etapas para preparar o mesmo banco de dados.

  • Faça o download do arquivo de backup do AdventureWorks2016_EXT no GitHub

    Baixar banco de dados

  • Restaure esse banco de dados na sua instância SQL no modo RECUPERAÇÃO

    Restaurar banco de dados

  • Baixar [sql-server-2016-samples.zip] do GitHub
  • Extraia a pasta e execute todos os scripts (exceto cleanup.sql) da pasta JSON no diretório
            AdventureWorks2016_EXT. Você também precisa de um serviço de pesquisa de texto completo para json.indexes.sql, mas não há problema em
            ignore os erros relacionados ao texto completo deste artigo

    Download de scripts

Nesta etapa, podemos usar a função JSON para consultar dados no banco de dados de exemplo AdventureWorks2016_EXT.

Ver dados de amostra

Funções SQL em combinação com a função JSON_QUERY ()

Podemos usar funções SQL como SQL CONCAT em combinação com funções JSON_QUERY. Você pode baixar
    Banco de dados WideWorldImporters.

Funções SQL

Podemos usar PARA CAMINHO JSON argumento para formatar a saída no formato JSON. Também permite que você
    forneça um elemento raiz na cadeia JSON. Na consulta abaixo, usamos a raiz como Itens.

Argumento FOR JSON PATH em Dados JSON

Você pode clicar no hiperlink JSON e ele fornece JSON, como mostrado abaixo.

Exibir hiperlink JSON

Você pode copiar o JSON e colá-lo no formatador JSON. Ele fornece o seguinte JSON formatado.

JSON formatado

Diferença entre as funções JSON_VALUE e JSON_Modify

Função JSON_VALUE

Função JSON_MODIFY

Retorna um valor escalar de JSON.

Nós obtemos um objeto ou uma matriz do JSON.

Tipo de dados de saída – Nvarhcar (4000)

Tipo de dados de saída – Nvarchar (máx)

Retorna um valor NULL se tentarmos retornar uma matriz ou objeto.

Saída da função JSON_VALUE

Ele retorna um valor NULL na saída se tentarmos recuperar um valor escalar.

Saída da função JSON_Modify

Não podemos recuperar um objeto JSON usando a função JSON_VALUE (). Retorna um valor NULL neste caso.

Função JSON_Value ()

Não podemos recuperar um elemento de matriz usando essa função. Nós obtemos o valor NULL para o elemento da matriz.

Função JSON_Modify ()

Conclusão

Neste artigo, exploramos a função JSON_QUERY () para recuperar o objeto e a matriz JSON dos dados JSON. É um
    função útil para trabalhar com dados JSON do SQL Server. Você deve explorar essas funções e estar familiarizado com as
    Notação de objeto Javascript.

Rajendra Gupta
Últimas mensagens de Rajendra Gupta (ver todos)

cupom com desconto - o melhor site de cupom de desconto cupomcomdesconto.com.br
Leia Também  Importar dados de vários arquivos do Excel para tabelas do SQL Server usando um pacote SSIS