Utilizando JSON no PostgreSQL com JSONB Data Types

A adoção de bancos de dados não-relacionais (NoSQL) como o MongoDB e o Redis vem crescendo a cada dia, porém se você você utiliza o Postgres, saiba que desde a versão 9.4 é possível armazenar e manipular dados em JSON de forma eficiente com os JSONB Data Types.

Quando e porque utilizar?

Posso citar dois cenários em que uma coluna do tipo JSON na sua tabela pode ser muito útil:

1 — Quero armazenar um array de valores dentro do meu registro.

Quando temos este tipo de relação “1 para muitos” ou “1:N” normalmente precisamos criar uma outra tabela para que possamos interligar os dados. Porém em certos casos só queremos armazená-los sem se preocupar com chaves estrangeiras. Um bom exemplo seria uma tabela Pessoa que teria uma coluna Interesses onde podemos gravar um array desta forma: “[‘programação’, ‘música’, ‘esportes’]”

2 — Quero armazenar dados que variam para cada registro

Existem casos onde queremos gravar alguns dados que não são exatamente fixos. Com SQL teríamos que criar uma coluna para cada um desses dados, fazendo com que ela fique muito extensa. Uma solução seria usar somente uma coluna to tipo jsonb onde gravaríamos um JSON com todos esses campos em forma de chave e valor. Exemplo: “{ “cor-favorita”: “azul”, “idade”: 32, “aprovado”: true }”

Criando uma tabela com Colunas do tipo jsonb:

CREATE TABLE pessoa ( 
 id serial NOT NULL,
 nome text,
 interesses jsonb,
 informacoes_adicionais jsonb
);

Agora vamos inserir alguns registros nesta tabela:

INSERT INTO pessoa VALUES (1, ‘João’, ‘[“futebol”, “natação”]’, ‘{“idade”: 28, “time”: “Chapecoense”}’);
INSERT INTO pessoa VALUES (2, ‘Maria’, ‘[“leitura”, “programação”, “dança”]’, ‘{“idade”: 39, “trabalha-com-programacao”: true, “area”: “back-end”}’);
INSERT INTO pessoa VALUES (3, ‘Ana’, ‘[“programação”]’, ‘{“idade”: 29, “trabalha-com-programacao”: false, “area”: “front-end”, “areas-de-interesse”: [“mobile”, “design”]}’);

Veja que para os campos do tipo jsonb inserimos strings no formato JSON. A grande vantagem é que podemos armazenar qualquer tipo de dados, desde que no final ele seja um JSON válido. Então é recomendado se certificar que os campos estejam sempre corretos, caso contrário recebemos um erro de sintaxe.

Buscando dados

SELECT nome, interesses, informacoes_adicionais->’idade’ FROM pessoa

Neste Select trouxemos todos os dados das pessoas, porém extraímos somente o campo “idade” da coluna “informacoes-adicionais”.

Podemos utilizar uma query buscando os registros contidos dentro de um jsonb array com a função “jsonb_array_elements_text”

SELECT jsonb_array_elements_text(interesses) FROM pessoa WHERE nome = ‘Maria’

Então temos este resultado:

“leitura”
“programação”
“dança”

Filtrando dados

Da mesma forma que podemos extrair somente os elementos desejados em um Select, para realizar um filtro a operação é bastante semelhante, observe:

SELECT nome, informacoes_adicionais->’area’ as area FROM pessoa 
WHERE 
informacoes_adicionais ? ‘area’ 
AND informacoes_adicionais->’trabalha-com-programacao’ = ‘true’

Nesta consulta estamos fazendo duas condições. Primeiro queremos buscar somente os registros que contenham a chave “area” dentro do json "informacoes_adicionais", independente do seu valor. Para isso foi usado o operador “?”. A segunda condição foi filtrar todos os registros que possuam a chave "trabalha-com-programacao" e que o valor dela seja “true”. Dessa vez o “?” não foi necessário porque uma vez que já informamos um valor específico desta chave, o Postgres entende que se a chave nem existir então ele não será filtrado.

Com isso obtemos o seguinte resultado:

nome    | area
“Maria” | “back-end”

Para casos onde precisamos buscar dentro de um json um valor com tipos diferentes, é necessário convertê-lo para o tipo desejado. No exemplo abaixo precisei buscar todas as pessoas com idade posterior a 28 anos. Para isso a chave “idade” de “informacoes_adicionais” foi convertida primeiramente de jsonb para text, e depois de text para integer.

SELECT jsonb_array_elements_text(interesses) FROM pessoa
WHERE (informacoes_adicionais->’idade’)::text::integer > 28

Também conseguimos verificar se um determinado item está dentro de um array de jsonb utilizando o operador @>. Na query abaixo buscamos todas as pessoas que possuem interesse por programação:

SELECT * FROM pessoa WHERE interesses @> ‘[“programação”]’

Criando Índices

Uma vez que a quantidade de registros de uma tabela vai crescendo, é imprescindível que criemos índices para as nossas colunas afim de ganhar performance em nossas consultas. A boa notícia é que os tipos jsonb aceitam índices, inclusive em determinadas chaves dentro deles. Vamos imaginar um exemplo onde nossa tabela de pessoas teria milhares de registros e gostaríamos de buscar todas as pessoas que trabalham com programação. Criando um índice para esta determinada chave deixará nossa consulta muito mais rápida:

CREATE INDEX idx_trabalha_com_programacao ON pessoa ((informacoes_adicionais->’trabalha-com-programacao’));

Conclusão

O Postgres é sem dúvida um dos bancos de dados mais poderosos do mercado, e a possibilidade de trabalhar com dados json nos dá muita flexibilidade, onde podemos mesclar a modelagem de nossas tabelas de forma relacional e também não-relacional graças aos JSONB Data Types.