Search multiple columns in search indexes

Search indexes can index multiple tokenized columns, making queries on these columns more efficient. This page describes how to perform a search on multiple columns, which is a type of full-text search.

Perform a multi-column search

The structure of the search index ensures that queries don't need a distributed join, ensuring predictable performance of the queries. The distributed join is avoided due to the colocation of all tokens that correspond to a base table row on the same split.

For example, consider the following schema:

CREATETABLEAlbums(
AlbumIdSTRING(MAX)NOTNULL,
TitleSTRING(MAX),
StudioSTRING(MAX),
Title_TokensTOKENLISTAS(TOKENIZE_FULLTEXT(Title))HIDDEN,
Studio_TokensTOKENLISTAS(TOKENIZE_FULLTEXT(Studio))HIDDEN
)PRIMARYKEY(AlbumId);
CREATESEARCHINDEXAlbumsIndexONAlbums(Title_Tokens,Studio_Tokens);

CREATETABLEalbums(
albumidcharactervaryingNOTNULL,
titlecharactervarying,
studiocharactervarying,
title_tokensspanner.tokenlist
GENERATEDALWAYSAS(TOKENIZE_FULLTEXT(title))VIRTUALHIDDEN,
studio_tokensspanner.tokenlist
GENERATEDALWAYSAS(TOKENIZE_FULLTEXT(studio))VIRTUALHIDDEN,
)PRIMARYKEY(albumid);
CREATESEARCHINDEXalbumsindexONalbums(title_tokens,studio_tokens);

A query can now search two fields: Title_Tokens and Studio_Tokens.

SELECTAlbumId
FROMAlbums
WHERESEARCH(Title_Tokens,"fifth symphony")
ANDSEARCH(Studio_Tokens,"Blue Note Studio")

SELECTalbumid
FROMalbums
WHEREspanner.search(title_tokens,'fifth symphony')
ANDspanner.search(studio_tokens,'Blue Note Studio')

Spanner supports multi-column search queries in conjunction, disjunction, and negation operators in the WHERE clause. You can use all of the following types of queries with a search index:

• Conjunction: Find documents where Title has the term "car" and Studio has the term "sun".

  GoogleSQL

  SELECTAlbumId
  FROMAlbums
  WHERESEARCH(Title_Tokens,'car')ANDSEARCH(Studio_Tokens,'sun')
  

  PostgreSQL

  SELECTalbumid
  FROMalbums
  WHEREspanner.search(title_tokens,'car')ANDspanner.search(studio_tokens,'sun')
  

• Disjunction: Find documents where either Title has the term "car" or Studio has the term "sun"

  GoogleSQL

  SELECTAlbumId
  FROMAlbums
  WHERESEARCH(Title_Tokens,'car')ORSEARCH(Studio_Tokens,'sun')
  

  PostgreSQL

  SELECTalbumid
  FROMalbums
  WHEREspanner.search(title_tokens,'car')ORspanner.search(studio_tokens,'sun')
  

• Negation: Find all documents where Title doesn't contain the term "car".

  GoogleSQL

  SELECTAlbumId
  FROMAlbums
  WHERENOTSEARCH(Title_Tokens,'car')
  

  PostgreSQL

  SELECTalbumid
  FROMalbums
  WHERENOTspanner.search(title_tokens,'car')
  

  The rquery language can perform the same type of searches:

  GoogleSQL

  SELECTAlbumId
  FROMAlbums
  WHERESEARCH(Title_Tokens,'-car')
  

  PostgreSQL

  SELECTalbumid
  FROMalbums
  WHEREspanner.search(title_tokens,'-car')
  

  Both forms filter documents where Title is NULL. Tokenization and search functions are defined to return NULL on NULL input. SQL defines NOT NULL as NULL.

Additionally, you can reference the same TOKENLIST column multiple times.

SELECTAlbumId
FROMAlbums
WHERE(SEARCH(Title_Tokens,'car')ORSEARCH(Studio_Tokens,'sun'))
AND(SEARCH(Title_Tokens,'guy')ORSEARCH(Studio_Tokens,electric))

SELECTalbumid
FROMalbums
WHERE(spanner.search(title_tokens,'car')ORspanner.search(studio_tokens,'sun'))
AND(spanner.search(title_tokens,'guy')ORspanner.search(studio_tokens,'electric'))

Use either the rquery language or SQL to search for multiple terms in the same column. rquery is recommended due to its efficient query caching for parameterized queries. Aside from the better query cache hit rate, the rquery and SQL languages have the same latency and performance rates.

SELECTAlbumId
FROMAlbums
WHERESEARCH(Title_Tokens,'car OR guy')
SELECTAlbumId
FROMAlbums
WHERESEARCH(Title_Tokens,'car')ORSEARCH(Title_Tokens,'guy')

SELECTalbumid
FROMalbums
WHEREspanner.search(title_tokens,'car OR guy')
SELECTalbumid
FROMalbums
WHEREspanner.search(title_tokens,'car')ORspanner.search(title_tokens,'guy')

You can also use non-text conditions accelerated with search indexes in the same query with full-text search functions.

What's next

• Learn about full-text search queries.
• Learn how to rank search results.
• Learn how to perform a substring search.
• Learn how to paginate search results.
• Learn how to mix full-text and non-text queries.