Realiza una búsqueda con facetas

En esta página, se describe cómo usar facetas para una búsqueda de texto completo. Como parte del filtrado interactivo, una faceta es un valor de filtro potencial y el recuento de coincidencias para ese filtro. En los sitios web con esta capacidad, cuando ingresas una frase de búsqueda, obtienes una lista de resultados en el menú de navegación y una lista de facetas que puedes usar para acotar los resultados de la búsqueda, junto con la cantidad de resultados que se ajustan a cada categoría.

Por ejemplo, si usas la búsqueda "foo" para buscar álbumes, es posible que obtengas cientos de resultados. Si hay una faceta de géneros, puedes seleccionar por géneros, como "rock (250)", "R&B (50)" o "pop (150)".

En la búsqueda de texto completo de Spanner, puedes usar expresiones de SQL estándar y funciones de búsqueda de texto completo para el filtrado y el recuento filtrado. No es necesario que uses una sintaxis especial para usar facetas.

Agrega facetas para usar en las búsquedas de texto completo

En el siguiente ejemplo, se crea una tabla para los álbumes y se tokeniza el título de cada álbum. Esta tabla se usa para los ejemplos de esta 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));

Crea un índice de búsqueda en Title_Tokens. De manera opcional, puedes almacenar Title, Genres y Rating en el índice de búsqueda para evitar una unión inversa a la tabla base mientras se calculan las 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 ejemplo, inserta los siguientes datos en la tabla.

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)

Recupera valores de recuento para una sola faceta

En este ejemplo, se muestra cómo realizar un recuento de facetas en una faceta de calificación. Realiza una búsqueda de texto de "foo" en la columna Title_Tokens de la tabla 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            |

Cómo recuperar valores de recuento para varias facetas

En este ejemplo, se muestran los pasos para realizar el recuento de facetas en varias facetas. Realiza las siguientes acciones:

  1. Recupera los resultados de la búsqueda inicial: Realiza una búsqueda de texto de "foo" en la columna Title_Tokens de la tabla Albums.
  2. Calcula los recuentos de facetas: Luego, calcula los recuentos de las facetas Rating y 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

Específicamente, este ejemplo hace lo siguiente:

  • WITH search_results AS (...) recopila un gran conjunto de resultados de la búsqueda iniciales para usarlos en la primera página de resultados y en los cálculos de facetas.
  • SEARCH(Title_Tokens, "foo") es la búsqueda principal.
  • LIMIT 10000 limita el costo de la búsqueda reduciendo el conjunto de resultados a 10,000. Para las búsquedas muy amplias que podrían devolver millones de resultados, calcular los recuentos exactos de las facetas en todo el conjunto de datos puede ser costoso. Al limitar los resultados de la búsqueda, la consulta puede proporcionar rápidamente recuentos aproximados (límite inferior) de las facetas. Esto significa que los recuentos reflejan al menos esa cantidad de resultados, pero es posible que haya más resultados coincidentes más allá del límite de 10,000.
  • La subconsulta result_page genera la primera página de resultados de la búsqueda que se muestra al usuario. Selecciona solo los primeros 50 registros de search_results, ordenados por Likes y AlbumId. Esto es lo que el usuario ve inicialmente.
  • La subconsulta rating_counts calcula los recuentos de facetas para Rating. Agrupa todos los registros en search_results por su Rating y cuenta cuántos resultados se incluyen en cada categoría de calificación.
  • La subconsulta genres_counts calcula los recuentos de facetas para Genres. Como es un array, únelo con UNNEST(Genres) para tratar cada género dentro del array como una fila separada para el recuento.

Recupera páginas posteriores

Cuando consultas páginas sucesivas después de la consulta de facetas inicial, puedes reutilizar los recuentos de facetas que se devolvieron en la primera página.

Para obtener más información sobre cómo paginar, consulta Usa la paginación basada en claves.