Pular para o conteúdo principal

PostgreSQL #> Operador

As funções JSON do PostgreSQL são ferramentas poderosas para consultar dados JSON armazenados em seu banco de dados. O operador `#>` é usado especificamente para extrair subelementos JSON com base em uma matriz de caminho especificada.

O operador `#>` é utilizado para acessar dados JSON aninhados, especificando uma matriz de caminhos. Ele é frequentemente usado quando você precisa consultar ou filtrar partes específicas de uma estrutura JSON.

SELECT json_column #> '{path, to, element}' FROM table_name;

Nessa sintaxe, `json_column` é a coluna que contém dados JSON e `'{path, to, element}'` é a matriz de caminhos usada para navegar até o subelemento desejado.

Exemplos

1. Extração básica

SELECT data #> '{address, city}' FROM users;

Essa consulta extrai o valor do campo `city` do objeto `address` na coluna `data` JSON para cada linha da tabela `users`.

2. Extração de matrizes aninhadas

SELECT data #> '{orders, 0, product_name}' FROM customers;

Aqui, a consulta recupera o `product_name` do primeiro pedido na matriz `orders` dentro da coluna JSON `data`. Observe que as matrizes e os objetos JSON são indexados a partir de zero.

3. Usando em uma cláusula WHERE

SELECT * FROM products WHERE details #> '{specs, color}' = '"blue"';

Este exemplo demonstra a filtragem de linhas verificando se o campo `color` dentro do objeto `specs` na coluna JSON `details` é igual a "blue". O valor de comparação deve ser uma cadeia de caracteres JSON, por isso a necessidade de aspas duplas em torno de "blue".

Dicas e práticas recomendadas

  • Use os path arrays com sabedoria. Certifique-se de que a matriz de caminho reflita corretamente a estrutura JSON para evitar erros e obter resultados precisos.
  • Indexar dados JSON. Considere a possibilidade de criar um índice GIN em colunas JSON para melhorar o desempenho da consulta ao usar operadores JSON. Por exemplo: 
    CREATE INDEX idx_json_data ON table_name USING GIN (json_column);
  • Validar caminhos JSON. Antes de fazer a consulta, valide os caminhos JSON para garantir que eles existam e estejam estruturados conforme o esperado.
  • Combine com outras funções JSON. Use o operador `#>` juntamente com outras funções JSON (`->`, `->>`) para consultas mais complexas e manipulação de dados. Observe que `->` extrai objetos ou matrizes JSON, enquanto `->>` extrai texto.
  • Possíveis armadilhas. Tenha cuidado com caminhos inexistentes ou tipos de dados incompatíveis, que podem levar a resultados de consulta inesperados.