Note: This feature is available with the Spanner Enterprise edition and Enterprise Plus edition. For more information, see theSpanner editions overview.

This page describes how to perform a search that mixes full-text and non-textdata.

Perform a mixed full-text and non-text search

Search indexes supportfull-text, exact match, numeric columns, and JSON/JSONB columns. You can combinetext and non-text conditions in theWHERE clause similarly to multi-columnsearch queries. The query optimizer tries to optimize non-text predicates with asearch index. If that's not possible, Spanner evaluates thecondition for every row that matches the search index. Referenced columns notstored in the search index are fetched from the base table.

Consider the following example:

CREATETABLEAlbums(AlbumIdSTRING(MAX)NOTNULL,TitleSTRING(MAX),RatingFLOAT64,GenresARRAY<STRING(MAX)>,LikesINT64,CoverBYTES(MAX),Title_TokensTOKENLISTAS(TOKENIZE_FULLTEXT(Title))HIDDEN,Rating_TokensTOKENLISTAS(TOKENIZE_NUMBER(Rating))HIDDEN,Genres_TokensTOKENLISTAS(TOKEN(Genres))HIDDEN)PRIMARYKEY(AlbumId);CREATESEARCHINDEXAlbumsIndexONAlbums(Title_Tokens,Rating_Tokens,Genres_Tokens)STORING(Likes);

CREATETABLEalbums(albumidcharactervaryingNOTNULL,titlecharactervarying,ratingbigint,genrescharactervaryingNOTNULL,likesbigint,coverbytea,title_tokensspanner.tokenlistAS(spanner.tokenize_fulltext(title))VIRTUALHIDDEN,rating_tokensspanner.tokenlistAS(spanner.tokenize_number(rating))VIRTUALHIDDEN,genres_tokensspanner.tokenlistAS(spanner.token(genres))VIRTUALHIDDEN,PRIMARYKEY(albumid));CREATESEARCHINDEXalbumsindexONalbums(title_tokens,rating_tokens,genres_tokens)INCLUDE(likes);

The behavior of queries on this table include the following:

• Rating andGenres are included in the search index.Spanner accelerates conditions using search index postinglists.ARRAY_INCLUDES_ANY,ARRAY_INCLUDES_ALL are GoogleSQLfunctions and are not supported for PostgreSQL dialect.

  SELECTAlbumFROMAlbumsWHERERating >4ANDARRAY_INCLUDES_ANY(Genres,['jazz'])

• The query can combine conjunctions, disjunctions, and negations in any way,including mixing full-text and non-text predicates. This query is fullyaccelerated by the search index.

  SELECTAlbumFROMAlbumsWHERE(SEARCH(Title_Tokens,'car')ORRating >4)ANDNOTARRAY_INCLUDES_ANY(Genres,['jazz'])

• Likes is stored in the index, but the schema doesn't requestSpanner to build a token index for its possible values.Therefore, the full-text predicate onTitle and non-text predicate onRating is accelerated, but the predicate onLikes isn't. InSpanner, the query fetches all documents with the term "car"in theTitle and a rating more than 4, then it filters documents thatdon't have at least 1000 likes. This query uses a lot of resources if almostall albums have the term "car" in their title and almost all of them have arating of 5, but few albums have 1000 likes. In such cases, indexingLikes similarly toRating saves resources.

  GoogleSQL

  SELECTAlbumFROMAlbumsWHERESEARCH(Title_Tokens,'car')ANDRating >4ANDLikes>=1000

  PostgreSQL

  SELECTalbumFROMalbumsWHEREspanner.search(title_tokens,'car')ANDrating >4ANDlikes>=1000

• Cover isn't stored in the index. The following query does aback joinbetweenAlbumsIndex andAlbums to fetchCover for all matching albums.

  GoogleSQL

  SELECTAlbumId,CoverFROMAlbumsWHERESEARCH(Title_Tokens,'car')ANDRating >4

  PostgreSQL

  SELECTalbumid,coverFROMalbumsWHEREspanner.search(title_tokens,'car')ANDrating >4

What's next

• Learn aboutfull-text search queries.
• Learn how torank search results.
• Learn how toperform a substring search.
• Learn how topaginate search results.
• Learn how tosearch multiple columns.