Perform a search using facets

This page describes how to use facets for a full-text search. As part of interactive filtering, a facet is a potential filter value, and the count of matches for that filter. On websites with this capability, when you enter a search phrase, you get a list of results in the navigation menu, and there's a list of facets that you can use to narrow the search results, along with the number of results that fit into each category.

For example, if you use the query "foo" to search for albums, you might get hundreds of results. If there's a facet of genres, you could select by genres like "rock (250)," "r&b (50)," or "pop (150)".

In Spanner full-text search, you can use standard SQL expressions and full-text search functions for both filtering and filtered counting. You don't need to use special syntax to use facets.

Add facets to use for full-text searches

The following example creates a table for albums and tokenizes the title for each album. This table is used for examples on this page.

CREATETABLEAlbums(
AlbumIdINT64NOTNULL,
TitleSTRING(MAX),
RatingINT64,
GenresARRAY<STRING(MAX)>,
LikesINT64NOTNULL,
Title_TokensTOKENLISTAS(TOKENIZE_FULLTEXT(Title))HIDDEN,
)PRIMARYKEY(AlbumId);

CREATETABLEalbums(
albumidbigintNOTNULL,
titletext,
ratingbigint,
genrestext[],
likesbigintNOTNULL,
title_tokensspanner.TOKENLISTGENERATEDALWAYSAS(spanner.TOKENIZE_FULLTEXT(Title))VIRTUALHIDDEN,
PRIMARYKEY(albumid));

Create a search index on Title_Tokens. Optionally, you can store Title, Genres and Rating in the search index to avoid a backjoin to the base table while computing facets.

CREATESEARCHINDEXAlbumsIndex
ONAlbums(Title_Tokens)
STORING(Title,Genres,Rating)
ORDERBYLikesDESC;

CREATESEARCHINDEXalbumsindex
ONalbums(title_tokens)
INCLUDE(title,genres,rating)
ORDERBYlikesDESC

For this example, insert the following data into the table.

INSERTINTOAlbums(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)

INSERTINTOalbums(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)

Retrieve count values for a single facet

This example shows how to perform a facet count on a Rating facet. It performs a text search for "foo" within the Title_Tokens column of the Albums table.

SELECTRating,COUNT(*)ASresult_count
FROMAlbums
WHERESEARCH(Title_Tokens,"foo")
GROUPBYRating
ORDERBYRatingDESC
|Rating|result_count|
|--------|--------------|
|5|1|
|4|1|

SELECTrating,COUNT(*)ASresult_count
FROMalbums
WHEREspanner.SEARCH(title_tokens,'foo')
GROUPBYrating
ORDERBYratingDESC;
|rating|result_count|
|--------|--------------|
|5|1|
|4|1|

Retrieve count values for multiple facets

This example shows the steps for performing facet counting on multiple facets. It performs the following:

1. Retrieve the initial search results: it performs a text search for "foo" within the Title_Tokens column of the Albums table.
2. Calculate facet counts: it then computes counts for the Rating and Genres facets.

WITHsearch_resultsAS(
SELECTAlbumId,Title,Genres,Rating,Likes
FROMAlbums
WHERESEARCH(Title_Tokens,"foo")
ORDERBYLikesDESC,AlbumId
LIMIT10000
)
SELECT
-- Result set #1: First page of search results
ARRAY(
SELECTASSTRUCT*
FROMsearch_results
ORDERBYLikesDESC,AlbumId
LIMIT50
)asresult_page,
-- Result set #2: Number of results by rating
ARRAY(
SELECTASSTRUCTRating,COUNT(*)asresult_count
FROMsearch_results
GROUPBYRating
ORDERBYresult_countDESC,RatingDESC
)asrating_counts,
-- Result set #3: Number of results for top 5 genres
ARRAY(
SELECTASSTRUCTgenre,COUNT(*)asresult_count
FROMsearch_results
JOINUNNEST(Genres)genre
GROUPBYgenre
ORDERBYresult_countDESC,genre
LIMIT5
)asgenres_counts

WITHsearch_resultsAS(
SELECTalbumid,title,genres,rating,likes
FROMalbums
WHEREspanner.SEARCH(title_tokens,'foo')
ORDERBYlikesDESC,albumid
LIMIT10000
)
-- 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(
SELECTJSONB_BUILD_OBJECT(
'albumid',albumid,
'title',title,
'genres',genres,
'rating',rating,
'likes',likes
)
FROMsearch_results
ORDERBYlikesDESC,albumid
LIMIT50
)asresult_page,
-- Result set #2: Number of results by rating
ARRAY(
SELECTJSONB_BUILD_OBJECT(
'rating',rating,
'result_count',COUNT(*)
)
FROMsearch_results
GROUPBYrating
ORDERBYCOUNT(*)DESC,ratingDESC
)asrating_counts,
-- Result set #3: Number of results for top 5 genres
ARRAY(
SELECTJSONB_BUILD_OBJECT(
'genre',genre,
'result_count',COUNT(*)
)
FROM
search_results,
UNNEST(genres)ASgenre
GROUPBYgenre
ORDERBYCOUNT(*)DESC,genre
LIMIT5
)asgenres_counts

Specifically, this example does the following:

• WITH search_results AS (...) gathers a large set of initial search results to use for the first page of results and facet calculations.
• SEARCH(Title_Tokens, "foo") is the primary search query.
• LIMIT 10000 limits the cost of the search by reducing the result set to 10,000. For very broad searches that might return millions of results, calculating exact facet counts on the entire dataset can be expensive. By limiting search results, the query can quickly provide approximate (lower bound) facet counts. This means the counts reflect at least that many results, but there might be more matching results beyond the 10,000 limit.
• The result_page subquery produces the first page of search results displayed to the user. It selects only the top 50 records from search_results, ordered by Likes and AlbumId. This is what the user initially sees.
• the rating_counts subquery calculates the facet counts for Rating. It groups all the records in search_results by their Rating and counts how many results fall into each rating category.
• The genres_counts subquery calculates the facet counts for Genres. As it's an array, join with UNNEST(Genres) to treat each genre within the array as a separate row for counting.

Retrieve subsequent pages

When you query for successive pages after the initial facet query, you can reuse the facet counts returned from the first page.

For more information on how to paginate, see Use key-based pagination.