Tratando campos texto em formato JSON

Clever_Anjos — Thu, 25 Oct 2018 10:51:19 GMT

Histórico

Com o advento de bancos de dados não relacionais orientados a documentos (MongoDB e outros) e API´s para integração entre sistemas frequentemente em Qlik somos desafiados a tratar campos em formato JSON (para entender o que é este formato leia aqui)

Desafio

Em Qlik temos que tratar campos com informações como essa (coletada do Cartola)

url (tente abrir no seu browser) https://api.cartolafc.globo.com/partidas

Fazendo essa estrutura ficar um pouco mais "amigável" para a leitura

ou seja, o conteúdo do campo é uma string que representa uma série de tabelas aninhadas que não correspondem ao modelo tabular que conhecemos (Excel, CSV, QVD, SQL, etc).

So what?

Felizmente os conectores da Qlik (REST, MongoDB, etc) tratam essa complexidade convertendo o aninhamento de tabelas em uma série de tabelas "normais"

Mas e se você não tiver acesso a elas? E se precisar buscar isso em um arquivo texto, ou essa informação estiver em uma coluna de um banco e você precisar tratar essa informação?

As funções de string comuns do Qlik (Subfield, textbetween, mid, left, etc) são muito "capengas" para tratar esse tipo de informação

Uma Solução

Existe um parâmetro do comando LOAD (help) que é uma possível saída. Ele é muito pouco conhecido, quase um patinho feio. É o from_field (verifique abaixo a definição de sintaxe do LOAD)

LOAD [ distinct ] fieldlist
[(
from file [ format-spec ] | 
from_field fieldassource [format-spec]|
inline data [ format-spec ] |
resident table-label | autogenerate size ) |
extension pluginname.functionname([script] tabledescription
)]
[where criterion | while criterion ]
[group by groupbyfieldlist ]
[order by orderbyfieldlist ]

O que esse cara faz?

Ele busca os valores de um campo previamente carregado (lembra do Resident? Parecido, mas não é a mesma coisa). Ele tem um parâmetro (format-spec) que é justamente fazer o parse de JSON!!

Como usar?

Exemplo simples usando para tratar os dados do Cartola

1 - Crie uma conexão simples do tipo WebFile apontando para https://api.cartolafc.globo.com/partidas

Ou carregue qualquer JSON que você tiver a mão

json:
LOAD
@1 AS json_field
FROM [lib://Cartola (qlikcloud_qlikid_cuv)]
(
  txt,
  utf8,
  no labels,
  no eof,
  delimiter is '\t',
  URL is "https://api.cartolafc.globo.com/partidas"
);

Teremos uma tabela com 1 linha que tem uma string enorme com todos os dados de partidas,clubes etc.

Analisando-se a estrutura (recomendo usar o https://jsonformatter.curiousconcept.com/ para investigar a estrutura do documento) podemos ver que existe uma tabela "partidas/clubes" que armazena vários documentos (cada clube) e cada clube possui id, nome, etc e essas informações ficam na tabela "partidas/clubes/{id do clube}"

Vamos agora converter aquela tabela de uma 1 linha com uma string para uma tabela com 1 linha e "n" colunas (uma com o id interno de cada um dos documentos)

t:load * from_field(json,json_field) (json, Table is "partidas/clubes");

fazendo assim o Qlik irá gerar uma tabela com os id internos para cada um dos documentos

Agora vamos buscar na nossa base, todos os clubes, passando o seu id para recuperar as informações da tabela

for j = 1 to NoOfFields('t')
  let clube = FieldName(j,'t');
  if IsNum(clube) then // Tratando porque o Qlik armazena o nome da tabela como uma das colunas
    Clubes:
    Load
      $(clube) as key,
      id as clube_id,
      nome,
      abreviacao
    from_field(json,json_field) (json, Table is "partidas/clubes/$(clube)");
  endif
next

Fazendo isso, chegamos ao que queríamos

Se alguém conseguir otimizar o código, por favor compartilhe

Have fun, be Qlik, we love data!

Re: Tratando campos texto em formato JSON

SolangeOliveira — Fri, 26 Oct 2018 00:53:35 GMT

Perfeito!!

Obrigada cleveranjos por compartilhar esta solução. Resolveu um grande gargalo!!!

topic Tratando campos texto em formato JSON in Brasil