Trabalhando com arquivos JSON no PostgreSQL


json_logo.png & postgres.jpg

Recentemente, um amigo meu encontrou problemas para trabalhar com arquivos json por estar habituado apenas à arquivos csv. Embora existem várias formas automatizadas para converter arquivos json para csv (mas não existem garantias de que a saída seria aceitável) o foco era o de carregar os dados no PostgreSQL para sumarizações e outros tipos de tratamentos e cruzamentos, e este também é o foco deste post.

bolsa_familia.png
O arquivo em questão refere-se a um conjunto de dados relacionados as famílias beneficiadas pelo programa do governo Bolsa Família e foco deste post é o de apenas criar uma tabela no banco de dados, derivada do arquivo json, contendo uma observação por linha. O trabalho se concentrará em carregar o arquivo e analisar os dados.

Vamos criar uma tabela no banco de dados para acomodar o arquivo:

CREATE TABLE bolsa_familia_arquivo ( conteudo JSONB );

Para carregar os dados, faremos uso do psql (você precisará adaptar os dados de conexão de acordo o seu ambiente):

psql -c 'COPY bolsa_familia_arquivo (conteudo) FROM STDIN;' < bolsa_familia.json

Eu não recomendo rodar SELECT * FROM bolsa_familia_arquivo porque o resultado será apenas um string gigante e ilegível. Ao invés, podemos usar o comando jsonb_pretty e iremos limitar a saída para os primeiros 500 caracteres pois é suficiente para entendermos a hierarquia dos dados:

SELECT SUBSTRING(JSONB_PRETTY(conteudo),1,500)
  FROM bolsa_familia_arquivo;

A saída será semelhante a seguinte:

                            substring
-----------------------------------------------------------------
 {                                                              +
     "response": {                                              +
         "docs": [                                              +
             {                                                  +
                 "ibge": "350010",                              +
                 "anomes": "200401",                            +
                 "valor_repassado_bolsa_familia": 23430.0,      +
                 "qtd_familias_beneficiarias_bolsa_familia": 381+
             },                                                 +
             {                                                  +
                 "ibge": "350020",                              +
                 "anomes": "200401",                            +
                 "valor_repassado_bolsa_familia": 2205.0,       +
                 "qtd_familias_beneficiarias_bolsa_familia": 42 +
             },                                                 +
             {                                                  +

(1 row)

Nessa saída, podemos verificar que o arquivo possui uma chave chamada “response” e dentro dela uma outra chave chamada “docs”, sendo este último um array de objetos; são justamente esses objetos que compões as observações que desejamos transformar em registros em uma tabela, cada chave dos objetos (no caso, “ibge”, “anomes”, “valor_repassado_bolsa_familia” e “qtd_familias_beneficiarias_bolsa_familia”) devem ser transformadas em colunas na tabela destino.

Vamos criar agora a tabela destino assumindo que os tipos dos dados são exatamente os que estamos visualizando na, humilde, amostra anterior1:

CREATE TABLE bolsa_familia (
    ibge                                     INTEGER       NOT NULL ,
    anomes                                   INTEGER       NOT NULL ,
    valor_repassado_bolsa_familia            NUMERIC(99,0) NOT NULL ,
    qtd_familias_beneficiarias_bolsa_familia INTEGER       NOT NULL
);

E agora vamos popular essa tabela (com uma instrução já não tão simplista):

INSERT INTO bolsa_familia
SELECT (e->>'ibge')::INTEGER,
       (e->>'anomes')::INTEGER,
       (e->>'valor_repassado_bolsa_familia')::NUMERIC(99,2),
       (e->>'qtd_familias_beneficiarias_bolsa_familia')::INTEGER
  FROM (SELECT jsonb_array_elements(conteudo->'response'->'docs') AS e
        FROM bolsa_familia_arquivo ) AS t;

Cuja saída:

INSERT 0 109005

Nos mostra quantas observações existiam no arquivo json.

A partir deste ponto, já é possível realizar qualquer tipo de trabalho como se a carga de dados tivesse sido feita com um arquivo csv.

Footnotes:

1

Normalmente, é uma presunção irresponsável.
Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

w

Conectando a %s