Faça uma pesquisa através de filtros

Esta página descreve como usar facetas para uma pesquisa de texto completo. Como parte da filtragem interativa, uma faceta é um valor de filtro potencial e a quantidade de correspondências para esse filtro. Em Websites com esta capacidade, quando introduz uma frase de pesquisa, recebe uma lista de resultados no menu de navegação e uma lista de facetas que pode usar para restringir os resultados da pesquisa, juntamente com o número de resultados que se enquadram em cada categoria.

Por exemplo, se usar a consulta "foo" para pesquisar álbuns, pode obter centenas de resultados. Se existir uma faceta de géneros, pode selecionar por géneros, como "rock (250)", "r&b (50)" ou "pop (150)".

Na pesquisa de texto completo do Spanner, pode usar expressões SQL padrão e funções de pesquisa de texto completo para filtragem e contagem filtrada. Não precisa de usar uma sintaxe especial para usar facetas.

Adicione facetas a usar para pesquisas de texto completo

O exemplo seguinte cria uma tabela para álbuns e tokeniza o título de cada álbum. Esta tabela é usada para exemplos nesta página.

GoogleSQL 

CREATE TABLE Albums (
  AlbumId INT64 NOT NULL,
  Title STRING(MAX),
  Rating INT64,
  Genres ARRAY<STRING(MAX)>,
  Likes INT64 NOT NULL,
  Title_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(Title)) HIDDEN,
) PRIMARY KEY(AlbumId);

PostgreSQL 

CREATE TABLE albums (
  albumid bigint NOT NULL,
  title text,
  rating bigint,
  genres text[],
  likes bigint NOT NULL,
  title_tokens spanner.TOKENLIST GENERATED ALWAYS AS (spanner.TOKENIZE_FULLTEXT(Title)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

Crie um índice de pesquisa em Title_Tokens. Opcionalmente, pode armazenar Title,Genres e Rating no índice de pesquisa para evitar uma junção inversa à tabela base ao calcular as facetas.

GoogleSQL 

CREATE SEARCH INDEX AlbumsIndex
ON Albums (Title_Tokens)
STORING (Title, Genres, Rating)
ORDER BY Likes DESC;

PostgreSQL 

CREATE SEARCH INDEX albumsindex
ON albums (title_tokens)
INCLUDE (title, genres, rating)
ORDER BY likes DESC

Para este exemplo, insira os seguintes dados na tabela.

GoogleSQL 

INSERT INTO Albums (AlbumId, Title, Rating, Genres, Likes) VALUES
(1, "The Foo Strike Again", 5, ["Rock", "Alternative"], 600),
(2, "Who are the Who?", 5, ["Progressive", "Indie"], 200),
(3, "No Foo For You", 4, ["Metal", "Alternative"], 50)

PostgreSQL 

INSERT INTO albums (albumid, title, rating, genres, likes) VALUES
(1, 'The Foo Strike Again', 5,'{"Rock", "Alternative"}', 600),
(2, 'Who are the Who?', 5,'{"Progressive", "Indie"}', 200),
(3, 'No Foo For You', 4,'{"Metal", "Alternative"}', 50)

Obtenha valores de contagem para uma única faceta

Este exemplo mostra como fazer uma contagem de facetas numa faceta de classificação. Executa uma pesquisa de texto de "foo" na coluna Title_Tokens da tabela Albums.

GoogleSQL 

SELECT Rating, COUNT(*) AS result_count
FROM Albums
WHERE SEARCH(Title_Tokens, "foo")
GROUP BY Rating
ORDER BY Rating DESC

| Rating | result_count |
|--------|--------------|
| 5      | 1            |
| 4      | 1            |

PostgreSQL 

SELECT rating, COUNT(*) AS result_count
FROM albums
WHERE spanner.SEARCH(title_tokens, 'foo')
GROUP BY rating
ORDER BY rating DESC;

| rating | result_count |
|--------|--------------|
| 5      | 1            |
| 4      | 1            |

Obtenha valores de contagem para várias facetas

Este exemplo mostra os passos para realizar a contagem de facetas em várias facetas. Faz o seguinte:

  1. Obter os resultados da pesquisa iniciais: faz uma pesquisa de texto por "foo" na coluna Title_Tokens da tabela Albums.
  2. Calcular contagens de facetas: em seguida, calcula as contagens das facetas Rating e Genres.

GoogleSQL 

WITH search_results AS (
  SELECT AlbumId, Title, Genres, Rating, Likes
  FROM Albums
  WHERE SEARCH(Title_Tokens, "foo")
  ORDER BY Likes DESC, AlbumId
  LIMIT 10000
)

SELECT
-- Result set #1: First page of search results
ARRAY(
  SELECT AS STRUCT *
  FROM search_results
  ORDER BY Likes DESC, AlbumId
  LIMIT 50
) as result_page,
-- Result set #2: Number of results by rating
ARRAY(
  SELECT AS STRUCT Rating, COUNT(*) as result_count
  FROM search_results
  GROUP BY Rating
  ORDER BY result_count DESC, Rating DESC
) as rating_counts,
-- Result set #3: Number of results for top 5 genres
ARRAY(
  SELECT AS STRUCT genre, COUNT(*) as result_count
  FROM search_results
  JOIN UNNEST(Genres) genre
  GROUP BY genre
  ORDER BY result_count DESC, genre
  LIMIT 5
) as genres_counts

PostgreSQL 

WITH search_results AS (
  SELECT albumid, title, genres, rating, likes
  FROM albums
  WHERE spanner.SEARCH(title_tokens, 'foo')
  ORDER BY likes DESC, albumid
  LIMIT 10000
)

-- The pattern ARRAY(SELECT TO_JSONB ...) enables returning multiple nested
-- result sets in the same query.
SELECT
  -- Result set #1: First page of search results
  ARRAY(
  SELECT JSONB_BUILD_OBJECT(
    'albumid', albumid,
    'title', title,
    'genres', genres,
    'rating', rating,
    'likes', likes
    )
  FROM search_results
  ORDER BY likes DESC, albumid
  LIMIT 50
) as result_page,
-- Result set #2: Number of results by rating
ARRAY(
  SELECT JSONB_BUILD_OBJECT(
    'rating', rating,
    'result_count', COUNT(*)
    )
  FROM search_results
  GROUP BY rating
  ORDER BY COUNT(*) DESC, rating DESC
) as rating_counts,
-- Result set #3: Number of results for top 5 genres
ARRAY(
  SELECT JSONB_BUILD_OBJECT(
    'genre', genre,
    'result_count', COUNT(*)
    )
  FROM
    search_results,
    UNNEST(genres) AS genre
  GROUP BY genre
  ORDER BY COUNT(*) DESC, genre
  LIMIT 5
) as genres_counts

Especificamente, este exemplo faz o seguinte:

  • WITH search_results AS (...) reúne um grande conjunto de resultados da pesquisa iniciais para usar na primeira página de resultados e nos cálculos de facetas.
  • SEARCH(Title_Tokens, "foo") é a consulta de pesquisa principal.
  • LIMIT 10000 limita o custo da pesquisa reduzindo o conjunto de resultados para 10 000. Para pesquisas muito amplas que podem devolver milhões de resultados, o cálculo das contagens exatas de facetas no conjunto de dados completo pode ser dispendioso. Ao limitar os resultados da pesquisa, a consulta pode fornecer rapidamente contagens de facetas aproximadas (limite inferior). Isto significa que as contagens refletem, pelo menos, esse número de resultados, mas podem existir mais resultados correspondentes além do limite de 10 000.
  • A subconsulta result_page produz a primeira página de resultados da pesquisa apresentada ao utilizador. Seleciona apenas os 50 principais registos de search_results, ordenados por Likes e AlbumId. É isto que o utilizador vê inicialmente.
  • A subconsulta rating_counts calcula as contagens de facetas para Rating. Agrupa todos os registos em search_results pelo respetivo Rating e conta quantos resultados se enquadram em cada categoria de classificação.
  • A subconsulta genres_counts calcula as contagens de facetas para Genres. Como é uma matriz, junte com UNNEST(Genres) para tratar cada género na matriz como uma linha separada para a contagem.

Obtenha páginas subsequentes

Quando consulta páginas sucessivas após a consulta de facetas inicial, pode reutilizar as contagens de facetas devolvidas pela primeira página.

Para mais informações sobre como paginar, consulte o artigo Use a paginação baseada em chaves.