ファセットを使用して検索を行う

このページでは、全文検索でファセットを使用する方法について説明します。インタラクティブ フィルタリングの一部として、ファセットはフィルタ値の候補と、そのフィルタの一致数です。この機能が実装されているウェブサイトでは、検索語句を入力すると、ナビゲーション メニューに結果のリストが表示されます。また、検索結果を絞り込むために使用できるファセットのリストと、各カテゴリに該当する結果の数も表示されます。

たとえば、「foo」というクエリを使用してアルバムを検索すると、数百件の結果が返されることがあります。ジャンルのファセットがある場合は、「ロック（250）」、「R&B（50）」、「ポップ（150）」などのジャンルで選択できます。

Spanner の全文検索では、フィルタリングとフィルタリングされたカウントの両方に標準 SQL 式と全文検索関数を使用できます。ファセットを使用するために特別な構文を使用する必要はありません。

全文検索に使用するファセットを追加する

次の例では、アルバムのテーブルを作成し、各アルバムのタイトルをトークン化します。このテーブルは、このページの例で使用されています。

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

Title_Tokens に検索インデックスを作成します。必要に応じて、Title、Genres、Rating を検索インデックスに保存して、ファセットの計算中にベーステーブルへのバックジョインを回避できます。

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

この例では、次のデータをテーブルに挿入します。

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)

単一のファセットのカウント値を取得する

この例では、Rating ファセットでファセット カウントを実行する方法を示します。Albums テーブルの Title_Tokens 列内で「foo」のテキスト検索を実行します。

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            |

複数のファセットのカウント値を取得する

この例では、複数のファセットでファセット カウントを実行する手順を示します。次の処理を行います。

  1. 最初の検索結果を取得します。Albums テーブルの Title_Tokens 列で「foo」のテキスト検索を実行します。
  2. ファセット数を計算する: Rating ファセットと 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

具体的には、この例では次の処理を行います。

  • WITH search_results AS (...) は、結果の最初のページとファセットの計算に使用する初期検索結果の大きなセットを収集します。
  • SEARCH(Title_Tokens, "foo") はプライマリ検索クエリです。
  • LIMIT 10000 は、結果セットを 10,000 に減らすことで、検索の費用を制限します。数百万件の結果が返される可能性のある非常に広範な検索の場合、データセット全体で正確なファセット数を計算すると、コストが高くなる可能性があります。検索結果を制限することで、クエリは近似値（下限）のファセット数をすばやく提供できます。つまり、カウントには少なくともその数の結果が反映されますが、10,000 件の制限を超える一致する結果が存在する可能性があります。
  • result_page サブクエリは、ユーザーに表示される検索結果の最初のページを生成します。Likes と AlbumId で並べ替えられた search_results の上位 50 件のレコードのみが選択されます。ユーザーに最初に表示される画面です。
  • rating_counts サブクエリは、Rating のファセット数を計算します。search_results 内のすべてのレコードを Rating でグループ化し、各評価カテゴリに分類される結果の数をカウントします。
  • genres_counts サブクエリは、Genres のファセット数を計算します。配列であるため、UNNEST(Genres) で結合して、配列内の各ジャンルをカウント用の個別の行として扱います。

後続のページを取得する

最初のファセット クエリの後に連続するページをクエリする場合は、最初のページから返されたファセット数を再利用できます。

ページネーションの方法については、キーベースのページネーションを使用するをご覧ください。