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.

2 comentários sobre “Trabalhando com arquivos JSON no PostgreSQL

    • Faz tempo que escrevi esse post e não sei dizer se eu não era familiarizado o suficiente com essas funções que citou ou se eu as descartei para este exemplo.

      Curtir

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 )

Conectando a %s