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

This page describes theSEARCH function and the enhanced query mode, which areused to performfull-text searchqueries on Spanner tables.

Query a search index

Spanner provides theSEARCHfunction to use for search indexqueries. An example use case would be an application where users enter text in asearch box and the application sends the user input directly into theSEARCHfunction. TheSEARCH function would then use a search index to find that text.

TheSEARCH function requires two arguments:

• A search index name
• A search query

TheSEARCH function only works when a search index is defined. TheSEARCHfunction can be combined with any arbitrary SQL constructs, such as filters,aggregations, or joins.

TheSEARCH function can't be used with transaction queries.

The following query uses theSEARCH function to return all albums that haveeitherfriday ormonday in the title:

SELECTAlbumIdFROMAlbumsWHERESEARCH(AlbumTitle_Tokens,'friday OR monday')

SELECTalbumidFROMalbumsWHEREspanner.search(albumtitle_tokens,'friday OR monday')

Search query

Search queries use theraw searchquerysyntax by default. Alternative syntaxes can be specified using theSEARCHdialectargument.

rquery dialect

The default dialect israw search query.Spanner uses a domain-specific language (DSL) calledrquery.

The rquery language follows the same rules as theplain-text tokenizerwhen splitting the input search query into distinct terms. This includessegmentation of Asian languages.

For information about using rquery, seerquery syntax.

words dialect

The words dialect is like rquery, but simpler. It doesn't use any specialoperators. For example,OR is treated as a search term instead of adisjunction operator. The double quotes are handled as punctuations rather thana phrase search and they are ignored.

With the words dialect,AND is implicitly applied to all terms, and isrequired during matching. It follows the same rules as the plain-text tokenizerwhen splitting the input search query into terms.

For information about using the words dialect, seewordssyntax.

words_phrase dialect

The words_phrase dialect doesn't use any special operators and all terms aretreated as a phrase, meaning the terms are required to be adjacent and in theorder specified.

Same as rquery, the words_phrase dialect follows the same rules as theplain-text tokenizer when splitting the input search query into terms.

For information about using the words_phrase dialect, seewords phrasesyntax.

Enhanced query mode

Spanner offers two full-text search modes: a basic token-basedsearch and a more advanced mode calledenhance_query. When enabled,enhance_query expands the search query to include related terms and synonyms,increasing the likelihood of finding relevant results.

To enable this option, set the optional argumentenhance_query=>true in theSEARCH function. For example, the search queryhotl cal matches the albumHotel California.

SELECTAlbumIdFROMAlbumsWHERESEARCH(AlbumTitle_Tokens,'hotl cal',enhance_query=>true)

SELECTalbumidFROMalbumsWHEREspanner.search(albumtitle_tokens,'hotl cal',enhance_query=>true)

Theenhance_query mode is a query-time option. It doesn't affect tokenization.You can use the same search index with or withoutenhance_query.

Google is continuously improving the query enhancement algorithms. As aresult, a query withenhance_query == true might yield slightly differentresults over time.

When theenhance_query mode is enabled, it might increase the number of termsthat theSEARCH function is looking for which could slightly elevate latency.

SQL query requirements

There are several conditions that a SQL query must meet to use a search index.If these conditions aren't met, the query uses either an alternative query planor fails if no alternative plan exists.

Queries must meet the following conditions:

• SEARCH functionandSEARCH_SUBSTRINGfunctions require a search index.Spanner doesn't support these functions in queries againstthe base table or secondary indexes.

• Partitioned indexesmust have all partition columns bound by an equality condition in theWHERE clause of the query.

  For example, if a search index is defined asPARTITION BY x, y, thequery must have a conjunct in theWHERE clause ofx = <parameter orconstant> AND y = <parameter or constant>. That search index isn'tconsidered by the query optimizer if such a condition is missing.

• AllTOKENLIST columns referenced bySEARCH andSEARCH_SUBSTRINGoperators must be indexed in the same search index.

  For example, consider the following table and index definition:

  GoogleSQL

  CREATETABLEAlbums(AlbumIdSTRING(MAX)NOTNULL,AlbumTitleSTRING(MAX),AlbumStudioSTRING(MAX),AlbumTitle_TokensTOKENLISTAS(TOKENIZE_FULLTEXT(AlbumTitle))HIDDEN,AlbumStudio_TokensTOKENLISTAS(TOKENIZE_FULLTEXT(AlbumStudio))HIDDEN)PRIMARYKEY(AlbumId);CREATESEARCHINDEXAlbumsTitleIndexONAlbums(AlbumTitle_Tokens);CREATESEARCHINDEXAlbumsStudioIndexONAlbums(AlbumStudio_Tokens);

  PostgreSQL

  CREATETABLEalbums(albumidcharactervaryingNOTNULL,albumtitlecharactervarying,albumstudiocharactervarying,albumtitle_tokensspanner.tokenlistGENERATEDALWAYSAS(spanner.tokenize_fulltext(albumtitle))VIRTUALHIDDEN,albumstudio_tokensspanner.tokenlistGENERATEDALWAYSAS(spanner.tokenize_fulltext(albumstudio))VIRTUALHIDDEN,PRIMARYKEY(albumid));CREATESEARCHINDEXalbumstitleindexONalbums(albumtitle_tokens);CREATESEARCHINDEXalbumsstudioindexONalbums(albumstudio_tokens);

  The following query fails because there's no single search index thatindexes bothAlbumTitle_Tokens andAlbumStudio_Tokens:

  GoogleSQL

  SELECTAlbumIdFROMAlbumsWHERESEARCH(AlbumTitle_Tokens,@p1)ANDSEARCH(AlbumStudio_Tokens,@p2)

  PostgreSQL

  This example uses query parameters$1 and$2 which are bound to'fast car' and 'blue note', respectively.

  SELECTalbumidFROMalbumsWHEREspanner.search(albumtitle_tokens,$1)ANDspanner.search(albumstudio_tokens,$2)

• If the sort order column is nullable, both the schema and the query mustexclude rows where the sort order column is NULL. For details, seeSearch index sort order.

• If the search index is NULL filtered, the query must include the sameNULL-filtering expression that's used in an index. SeeNULL-filtered searchindexesfor details.

• Search indexes andsearch functionsaren't supported in DML, partitioned DML, or partitioned queries.

• Search indexes andsearch functionsare typically used inread-only transactions.If application requirements allow stale results, you might be able toimprove latency by running search queries with a staleness duration of 10seconds or longer. For more information, seeRead stale data. This isparticularly useful for search queries that fan out to many index splits.

Search indexes andsearch functions arenot recommended inread-write transactions.During execution, search queries lock an entire index partition; as a result, ahigh rate of search queries in read-write transactions might cause lockconflicts leading to latency spikes. By default, search indexes are notautomatically selected in read-write transactions. If a query is forced to use asearch index in a read-write transaction it fails by default. It also fails ifthe query contains any of the search functions. This behavior can be overriddenwith the GoogleSQL@{ALLOW_SEARCH_INDEXES_IN_TRANSACTION=TRUE}statement-level hint (but queries are still prone to lock conflicts).

Once index eligibility conditions are met, the query optimizer tries toaccelerate non-text query conditions (likeRating > 4). If the search indexdoesn't include the appropriateTOKENLIST column, the condition isn'taccelerated and remains aresidual condition.

Query parameters

Search query arguments are specified as either a literal or aqueryparameter. Werecommend using query parameters for full-text search rather than stringliterals when arguments allow query parameter value.

Index selection

Spanner typically selects the most efficient index for a queryusing cost-based modeling. However, theFORCE_INDEX hint explicitly instructsSpanner to use a specific search index. For example, thefollowing shows how to force Spanner to use theAlbumsIndex:

SELECTAlbumIdFROMAlbums@{FORCE_INDEX=AlbumsIndex}WHERESEARCH(AlbumTitle_Tokens,"fifth symphony")

SELECTalbumidFROMalbums/*@force_index=albumsindex*/WHEREspanner.search(albumtitle_tokens,'fifth symphony')

If the specified search index isn'teligible, thequery fails, even if there are other eligible search indexes.

Snippets in search results

A snippet is a piece of text extracted from a given string that gives users asense of what a search result contains, and the reason why the result isrelevant to their query.

For example, Gmail uses snippets to indicate the portion of an email thatmatches the search query:

Having the database generate a snippet has several benefits:

1. Convenience: You don't need to implement logic to generate snippetsfrom a search query.
2. Efficiency: Snippets reduce the output size from the server.

TheSNIPPETfunction creates the snippet. It returns the relevant portion of the originalstring value along with positions of characters to highlight. The client canthen choose how to display the snippet to the end user (for example, usinghighlighted or bold text).

For example, the following usesSNIPPET to retrieve text fromAlbumTitle:

SELECTAlbumId,SNIPPET(AlbumTitle,"Fast Car")FROMAlbumsWHERESEARCH(AlbumTitle_Tokens,"Fast Car")

SELECTalbumid,spanner.snippet(albumtitle,'Fast Car')FROMalbumsWHEREspanner.search(albumtitle_tokens,'Fast Car')

What's next

• Learn how torank search results.
• Learn how toperform a substring search.
• Learn how topaginate search results.
• Learn how tomix full-text and non-text queries.
• Learn how tosearch multiple columns.