Hacer una búsqueda usando facetas

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

Por ejemplo, si usas la consulta "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, puede usar expresiones SQL estándar y funciones de búsqueda de texto completo para filtrar y contar los resultados filtrados. No es necesario que uses una sintaxis especial para usar las facetas.

Añadir facetas para usarlas en búsquedas de texto completo

En el siguiente ejemplo se crea una tabla de álbumes y se tokeniza el título de cada álbum. Esta tabla se usa en 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. Opcionalmente, puede almacenar Title, Genres y Rating en el índice de búsqueda para evitar una unión inversa a la tabla base al calcular 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

En 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)

Obtener los valores de recuento de una sola faceta

En este ejemplo se muestra cómo realizar un recuento de facetas en una faceta Rating. 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            |

Obtener valores de recuento de varias facetas

En este ejemplo se muestran los pasos para realizar el recuento de facetas en varias facetas. Hace lo siguiente:

  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 el número de facetas: a continuación, calcula el número de 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

En concreto, este ejemplo hace lo siguiente:

  • WITH search_results AS (...) recoge un gran conjunto de resultados de 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 consulta de búsqueda principal.
  • LIMIT 10000 limita el coste de la búsqueda reduciendo el conjunto de resultados a 10.000. En el caso de las búsquedas muy amplias que pueden devolver millones de resultados, calcular el número exacto de facetas en todo el conjunto de datos puede ser costoso. Al limitar los resultados de búsqueda, la consulta puede proporcionar rápidamente recuentos de facetas aproximados (límite inferior). Esto significa que los recuentos reflejan al menos ese número de resultados, pero puede haber más resultados coincidentes que superen el límite de 10.000.
  • La subconsulta result_page genera la primera página de resultados de búsqueda que se muestra al usuario. Solo se seleccionan los 50 primeros registros de search_results, ordenados por Likes y AlbumId. Esto es lo que ve el usuario al principio.
  • La subconsulta rating_counts calcula el número de facetas de Rating. Agrupa todos los registros de search_results por su Rating y cuenta cuántos resultados se incluyen en cada categoría de valoración.
  • La subconsulta genres_counts calcula el número de facetas de Genres. Como se trata de una matriz, únelos con UNNEST(Genres) para que cada género de la matriz se considere una fila independiente para el recuento.

Recuperar páginas posteriores

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

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