Fazer uma pesquisa usando atributos

Nesta página, descrevemos como usar facetas para uma pesquisa de texto completo. Como parte da filtragem interativa, uma faceta é um valor de filtro em potencial e a contagem de correspondências para esse filtro. Em sites com essa capacidade, quando você insere uma frase de pesquisa, recebe uma lista de resultados no menu de navegação e uma lista de refinamentos que podem ser usados para restringir os resultados da pesquisa, além do número de resultados que se encaixam em cada categoria.

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

Na pesquisa de texto completo do Spanner, é possível usar expressões SQL padrão e funções de pesquisa de texto completo para filtragem e contagem filtrada. Não é necessário usar uma sintaxe especial para usar facetas.

Adicionar atributos para usar em pesquisas de texto completo

O exemplo a seguir cria uma tabela para álbuns e tokeniza o título de cada um deles. Essa 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. Se quiser, armazene Title, Genres e Rating no índice de pesquisa para evitar uma junção de retorno à tabela de base ao calcular 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)

Recuperar valores de contagem para um único atributo

Este exemplo mostra como realizar uma contagem de facetas em uma faceta de classificação. Ela realiza uma pesquisa de texto por "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            |

Recuperar valores de contagem para vários atributos

Este exemplo mostra as etapas para realizar a contagem de atributos em vários atributos. Ele faz o seguinte:

  1. Recupera os resultados da pesquisa inicial: realiza uma pesquisa de texto por "foo" na coluna Title_Tokens da tabela Albums.
  2. Calcular contagens de refinamentos: depois, ele calcula as contagens dos refinamentos 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:

  • O WITH search_results AS (...) reúne um grande conjunto de resultados de pesquisa iniciais para usar na primeira página de resultados e nos cálculos de refinamento.
  • 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 retornar milhões de resultados, calcular contagens exatas de atributos em todo o conjunto de dados pode ser caro. Ao limitar os resultados da pesquisa, a consulta pode fornecer rapidamente contagens aproximadas (limite inferior) de facetas. Isso significa que as contagens refletem pelo menos essa quantidade de resultados, mas pode haver mais resultados correspondentes além do limite de 10.000.
  • A subconsulta result_page produz a primeira página de resultados da pesquisa exibida ao usuário. Ela seleciona apenas os 50 principais registros de search_results, ordenados por Likes e AlbumId. É isso que o usuário vê inicialmente.
  • A subconsulta rating_counts calcula as contagens de facetas para Rating. Ele agrupa todos os registros em search_results pelo 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, use UNNEST(Genres) para tratar cada gênero como uma linha separada para contagem.

Recuperar páginas subsequentes

Ao consultar páginas sucessivas após a consulta de refinamento inicial, é possível reutilizar as contagens de refinamento retornadas da primeira página.

Para mais informações sobre como fazer a paginação, consulte Usar paginação baseada em chaves.