Effectuer une recherche à l'aide de facettes

Cette page explique comment utiliser les facettes pour une recherche en texte intégral. Dans le filtrage interactif, une facette est une valeur de filtre potentielle, ainsi que le nombre de correspondances pour ce filtre. Sur les sites Web dotés de cette fonctionnalité, lorsque vous saisissez une expression de recherche, vous obtenez une liste de résultats dans le menu de navigation. Vous disposez également d'une liste de facettes que vous pouvez utiliser pour affiner les résultats de recherche, ainsi que du nombre de résultats correspondant à chaque catégorie.

Par exemple, si vous utilisez la requête "foo" pour rechercher des albums, vous pouvez obtenir des centaines de résultats. S'il existe une facette de genres, vous pouvez sélectionner des genres tels que "rock (250)", "r&b (50)" ou "pop (150)".

Dans la recherche en texte intégral Spanner, vous pouvez utiliser des expressions SQL standard et des fonctions de recherche en texte intégral pour le filtrage et le comptage filtré. Vous n'avez pas besoin d'utiliser une syntaxe spéciale pour utiliser les facettes.

Ajouter des facettes à utiliser pour les recherches en texte intégral

L'exemple suivant crée une table pour les albums et tokenise le titre de chaque album. Cette table est utilisée pour les exemples de cette page.

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

Créez un index de recherche sur Title_Tokens. Si vous le souhaitez, vous pouvez stocker Title, Genres et Rating dans l'index de recherche pour éviter une jointure inverse à la table de base lors du calcul des facettes.

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

Pour cet exemple, insérez les données suivantes dans le tableau.

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)

Récupérer les valeurs de nombre pour un seul attribut

Cet exemple montre comment effectuer un décompte des facettes sur une facette "Note". Elle effectue une recherche de texte pour "foo" dans la colonne Title_Tokens de la table 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            |

Récupérer les valeurs de nombre pour plusieurs facettes

Cet exemple montre comment effectuer le comptage des facettes sur plusieurs facettes. Il effectue les opérations suivantes :

  1. Récupérer les premiers résultats de recherche : effectue une recherche textuelle pour "foo" dans la colonne Title_Tokens de la table Albums.
  2. Calculer les nombres de facettes : il calcule ensuite les nombres pour les facettes Rating et 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

Plus précisément, cet exemple effectue les opérations suivantes :

  • WITH search_results AS (...) rassemble un grand nombre de résultats de recherche initiaux à utiliser pour la première page de résultats et les calculs des facettes.
  • SEARCH(Title_Tokens, "foo") est la requête de recherche principale.
  • LIMIT 10000 limite le coût de la recherche en réduisant l'ensemble de résultats à 10 000. Pour les recherches très larges qui peuvent renvoyer des millions de résultats, le calcul du nombre exact de facettes sur l'ensemble de données peut être coûteux. En limitant les résultats de recherche, la requête peut rapidement fournir des nombres approximatifs (limite inférieure) de facettes. Cela signifie que les nombres reflètent au moins autant de résultats, mais il peut y avoir d'autres résultats correspondants au-delà de la limite de 10 000.
  • La sous-requête result_page génère la première page de résultats de recherche affichée à l'utilisateur. Elle ne sélectionne que les 50 premiers enregistrements de search_results, classés par Likes et AlbumId. C'est ce que l'utilisateur voit au départ.
  • La sous-requête rating_counts calcule le nombre de facettes pour Rating. Elle regroupe tous les enregistrements de search_results par Rating et compte le nombre de résultats qui entrent dans chaque catégorie de classification.
  • La sous-requête genres_counts calcule les nombres de facettes pour Genres. Comme il s'agit d'un tableau, joignez-le avec UNNEST(Genres) pour traiter chaque genre du tableau comme une ligne distincte à comptabiliser.

Récupérer les pages suivantes

Lorsque vous demandez des pages successives après la requête d'attributs initiale, vous pouvez réutiliser les nombres d'attributs renvoyés par la première page.

Pour en savoir plus sur la pagination, consultez Utiliser la pagination basée sur des clés.