Consulta e filtragem de campos JSON no PostgreSQL

As funções JSON do PostgreSQL permitem que você armazene, consulte e manipule dados JSON nas tabelas do seu banco de dados. Eles são essenciais para aplicativos que exigem um esquema flexível ou que precisam processar dados JSON diretamente no banco de dados.

Compreensão de JSON e JSONB

`JSON` e `JSONB` são tipos de dados no PostgreSQL utilizados para armazenar dados JSON. O `JSON` armazena dados em um formato de texto, enquanto o `JSONB` armazena dados em um formato binário decomposto, permitindo indexação e consulta eficientes.

Uso

As funções JSON são utilizadas no PostgreSQL para acessar e filtrar dados JSON armazenados em colunas do tipo `JSON` ou `JSONB`. Eles permitem a recuperação e a manipulação de dados complexos, fornecendo métodos para extrair elementos, filtrar matrizes e modificar objetos JSON.

SELECT json_column->'key'
FROM table_name
WHERE json_column->>'key' = 'value';

Nessa sintaxe, `->` é usado para extrair um objeto JSON, enquanto `->>` extrai texto JSON.

Exemplos

1. Extração de um campo JSON

SELECT data->'name' AS name
FROM users;

Essa consulta extrai o campo `name` da coluna JSON `data` na tabela `users`.

2. Filtragem com JSONB

SELECT *
FROM orders
WHERE order_data->>'status' = 'shipped';

Aqui, a consulta filtra as tabelas na tabela `orders` em que a chave `status` na coluna `order_data` JSONB está definida como `shipped`.

3. Filtragem de matriz JSONB

SELECT id, info
FROM products
WHERE info->'tags' @> '["electronics"]';

Essa consulta seleciona `id` e `info` da tabela `products` em que a matriz `tags` na coluna `info` JSONB contém o valor "electronics".

4. Atualização de dados JSON

UPDATE users
SET data = jsonb_set(data, '{address, city}', '"New City"')
WHERE id = 1;

Essa consulta atualiza o campo `city` dentro do objeto `address` na coluna `data` JSONB para o usuário com `id` 1.

Dicas e práticas recomendadas

• Use JSONB para indexação. Prefira `JSONB` em vez de `JSON` para obter melhor desempenho com indexação e pesquisa.
• Aproveitar os índices GIN. Crie índices GIN em colunas JSONB para acelerar as consultas de contenção.
• Extraia com cuidado. Use `->` para objetos JSON e `->>` para extração de texto para reduzir erros de conversão de tipos.
• Mantenha as consultas específicas. Estruture suas consultas para minimizar a quantidade de dados JSON processados, melhorando o desempenho.
• Considere a normalização. Para campos JSON consultados com frequência, considere normalizar os dados em colunas ou tabelas separadas para aumentar a eficiência.
• Entenda as diferenças de desempenho. O `JSONB` é geralmente mais rápido para operações de leitura devido ao seu formato binário, mas pode exigir mais espaço de armazenamento.
• Cuidado com as implicações de armazenamento. O `JSONB` pode ter maiores requisitos de armazenamento devido à sua representação binária, portanto, escolha o tipo de dados com base em seu caso de uso específico.