Search functions in GoogleSQL

GoogleSQL for Spanner supports the following search functions.

Category	Functions	Description
Indexing	`TOKEN` `TOKENIZE_BOOL` `TOKENIZE_FULLTEXT` `TOKENIZE_NGRAMS` `TOKENIZE_NUMBER` `TOKENIZE_SUBSTRING`	Functions that you can use to create search indexes.
Retrieval and presentation	`SCORE` `SCORE_NGRAMS` `SEARCH` `SEARCH_NGRAMS` `SEARCH_SUBSTRING` `SNIPPET`	Functions that you can use to search for data, score the search result, or format the search result.

Function list

Name	Summary
`SCORE`	Calculates a relevance score of a `TOKENLIST` for a full-text search query. The higher the score, the stronger the match.
`SCORE_NGRAMS`	Calculates a relevance score of a `TOKENLIST` for a fuzzy search. The higher the score, the stronger the match.
`SEARCH`	Returns `TRUE` if a full-text search query matches tokens.
`SEARCH_NGRAMS`	Checks whether enough n-grams match the tokens in a fuzzy search.
`SEARCH_SUBSTRING`	Returns `TRUE` if a substring query matches tokens.
`SNIPPET`	Gets a list of snippets that match a full-text search query.
`TOKEN`	Constructs an exact match `TOKENLIST` value by tokenizing a `BYTE` or `STRING` value verbatim to accelerate exact match expressions in SQL.
`TOKENIZE_BOOL`	Constructs a boolean `TOKENLIST` value by tokenizing a `BOOL` value to accelerate boolean match expressions in SQL.
`TOKENIZE_FULLTEXT`	Constructs a full-text `TOKENLIST` value by tokenizing text for full-text matching.
`TOKENIZE_NGRAMS`	Constructs an n-gram `TOKENLIST` value by tokenizing a `STRING` value for matching n-grams.
`TOKENIZE_NUMBER`	Constructs a numeric `TOKENLIST` value by tokenizing numeric values to accelerate numeric comparison expressions in SQL.
`TOKENIZE_SUBSTRING`	Constructs a substring `TOKENLIST` value by tokenizing text for substring matching.

`SCORE`

SCORE(
  tokens,
  raw_search_query
  [, enhance_query=>{ TRUE | FALSE }]
  [, language_tag=>value]
  [, options=>value]
)

Description

Calculates a relevance score of a TOKENLIST for a full-text search query. The higher the score, the stronger the match.

Definitions

tokens: A TOKENLIST value that represents a list of full-text tokens.
raw_search_query: A STRING value that represents a raw search query.
enhance_query: A named argument with a BOOL value. The value determines whether to enhance the search query. For example, if enhance_query is performed on the search query containing a term classic, that query can expand to include similar terms such as classical or classics. The search query will not be enhanced if enhance_query call takes longer than the timeout.
- If TRUE, the search query will be enhanced to improve search quality.
- If FALSE (default), the search query will not be not enhanced.
language_tag: A named argument with a STRING value. The value contains an IETF BCP 47 language tag. You can use this tag to specify the language for raw_search_query. If the value for this argument is NULL, this function doesn't use a specific language. If this argument is not specified, NULL is used by default.
options: A named argument with a JSON value. The value represents the fine-tuning for the search scoring.
- bigram_weight: A multiplier for bigrams, which have matching terms adjacent to each other. The default is 2.0.
- idf_weight: A multiplier for term commonality. Hits on rare terms will score relatively higher than hits on common terms. This argument is only meaningful when enhance_query is TRUE. The default is 1.0.
- token_category_weights: A multiplier for each HTML category. The available categories are: small, medium, large, title.

Details

This function must reference a full-text TOKENLIST column in a table that is also indexed in a search index. To add a full-text TOKENLIST column to a table and to a search index, see the examples for this function.
This function requires the SEARCH function in the same SQL query.
This function returns 0 when tokens or raw_search_query is NULL.

Return type

FLOAT64

Examples

The following examples reference a table called Albums and a search index called AlbumsIndex.

The Albums table contains a column called DescriptionTokens, which tokenizes the input added to the Description column, and then saves those tokens in the DescriptionTokens column. Finally, AlbumsIndex indexes DescriptionTokens. Once DescriptionTokens is indexed, it can be used with the SCORE function.

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  Description STRING(MAX),
  DescriptionTokens TOKENLIST AS (TOKENIZE_FULLTEXT(Description)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(DescriptionTokens);

INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 1, 'classical album');
INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 2, 'classical and rock album');

The following query searches the column called Description for a token called classical album. If this token is found for singer ID 1, the matching Description are returned with the corresponding score. Both classical album and classical and rock album have the terms classical and album, but the first one has a higher score because the terms are adjacent.

SELECT
  a.Description, SCORE(a.DescriptionTokens, 'classical album') AS Score
FROM
  Albums a
WHERE
  SEARCH(a.DescriptionTokens, 'classical album');

/*--------------------------+---------------------*
 | Description              | Score               |
 +--------------------------+---------------------+
 | classical album          | 1.2818930149078369  |
 | classical and rock album | 0.50003194808959961 |
 *--------------------------+---------------------*/

The following query is like the previous one. However, scores are boosted more with bigram_weight on adjacent positions.

SELECT
  a.Description,
  SCORE(
    a.DescriptionTokens,
    'classical album',
    options=>JSON '{"bigram_weight": 3.0}'
  ) AS Score
FROM Albums a
WHERE SEARCH(a.DescriptionTokens, 'classical album');

/*--------------------------+---------------------*
 | Description              | Score               |
 +--------------------------+---------------------+
 | classical album          | 1.7417128086090088  |
 | classical and rock album | 0.50003194808959961 |
 *--------------------------+---------------------*/

The following query uses SCORE in the ORDER BY clause to get the row with the highest score.

SELECT a.Description
FROM Albums a
WHERE SEARCH(a.DescriptionTokens, 'classical album')
ORDER BY SCORE(a.DescriptionTokens, 'classical album') DESC
LIMIT 1;

/*--------------------------*
 | Description              |
 +--------------------------+
 | classical album          |
 *--------------------------*/

`SCORE_NGRAMS`

SCORE_NGRAMS(
  tokens,
  ngrams_query
  [, algorithm=>value]
)

Description

Calculates a relevance score of a TOKENLIST for a fuzzy search. The higher the score, the stronger the match.

Definitions

tokens: A TOKENLIST value that contains a list of ngrams tokens. You can generate a TOKENLIST using either TOKENIZE_SUBSTRING or TOKENIZE_NGRAMS, which tokenizes the source column directly. TOKENLIST generated from an expression isn't supported for scoring, for example, TOKENIZE_SUBSTRING(REGEXP_REPLACE(col, 'foo', 'bar)).
ngrams_query: A STRING value that represents a fuzzy search query.
algorithm: A named argument with a STRING value. The value specifies the scoring algorithm for the fuzzy search. The default value for this argument is trigrams, and currently it's the only supported algorithm.
- trigrams: Generates trigrams (n-grams with size 3) without duplication from the query, then also generates trigrams without duplication from the source column of the tokens. Matches are an intersection between query trigrams and source trigrams. The score is roughly calculated as (match_count / (query_trigrams + source_trigrams - match_count)).

Details

This function returns 0 when tokens or ngrams_query is NULL.
Unlike SEARCH_NGRAMS, this function requires access to the source column of tokens. Therefore, it is often advantageous to include the source column in SEARCH INDEX's STORING clause, to avoid a join with the base table. Please see index-only scans.

Return type

FLOAT64

Examples

The following examples reference a table called Albums and a search index called AlbumsIndex.

The Albums table contains a column DescriptionSubstrTokens which tokenizes Description column using TOKENIZE_SUBSTRING. Finally, AlbumsIndex stores Description, so that the query below does not have to join with the base table.

CREATE TABLE Albums (
  AlbumId INT64 NOT NULL,
  Description STRING(MAX),
  DescriptionSubstrTokens TOKENLIST AS
    (TOKENIZE_SUBSTRING(Description, ngram_size_max=>3)) HIDDEN,
) PRIMARY KEY (AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(DescriptionSubstrTokens)
  STORING(Description);

INSERT INTO Albums (AlbumId, Description) VALUES (1, 'rock album');
INSERT INTO Albums (AlbumId, Description) VALUES (2, 'classical album');

The following query scores Description with clasic albun, which is misspelled.

SELECT
  a.Description, SCORE_NGRAMS(a.DescriptionSubstrTokens, 'clasic albun') AS Score
FROM
  Albums a

/*-----------------+---------------------*
 | Description     | Score               |
 +-----------------+---------------------+
 | rock album      | 0.14285714285714285 |
 | classical album | 0.38095238095238093 |
 *-----------------+---------------------*/

The following query uses SCORE_NGRAMS in the ORDER BY clause to produce the row with the highest score.

SELECT a.Description
FROM Albums a
WHERE SEARCH_NGRAMS(a.DescriptionSubstrTokens, 'clasic albun')
ORDER BY SCORE_NGRAMS(a.DescriptionSubstrTokens, 'clasic albun') DESC
LIMIT 1

/*-----------------*
 | Description     |
 +-----------------+
 | classical album |
 *-----------------*/

`SEARCH`

SEARCH(
  tokens,
  raw_search_query
  [, enhance_query=>{ TRUE | FALSE }]
  [, language_tag=>value]
)

Description

Returns TRUE if a full-text search query matches tokens.

Definitions

tokens: A TOKENLIST value that is a list of full-text tokens.
raw_search_query: A STRING value that is a raw search query.
enhance_query: A named argument with a BOOL value. The value determines whether to enhance the search query. For example, if enhance_query is performed on the search query containing a term classic, that query can expand to include similar terms such as classical or classics. The search query will not be enhanced if enhance_query call takes longer than the timeout.
- If TRUE, the search query will be enhanced to improve search quality.
- If FALSE (default), the search query will not be not enhanced.
language_tag: A named argument with a STRING value. The value contains an IETF BCP 47 language tag. You can use this tag to specify the language for raw_search_query. If the value for this argument is NULL, this function doesn't use a specific language. If this argument is not specified, NULL is used by default.

Details

Returns TRUE if a TOKENLIST tokens is a match for raw_search_query.
This function must reference a full-text TOKENLIST column in a table that is also indexed in a search index. To add a full-text TOKENLIST column to a table and to a search index, see the examples for this function.
This function returns NULL when tokens or raw_search_query is NULL.
This function can only be used in the WHERE clause of a SQL query.

Return type

BOOL

Examples

The following examples reference a table called Albums and a search index called AlbumsIndex.

The Albums table contains a column called DescriptionTokens, which tokenizes the Description column using TOKENIZE_FULLTEXT, and then saves those tokens in the DescriptionTokens column. Finally, AlbumsIndex indexes DescriptionTokens. Once DescriptionTokens is indexed, it can be used with the SEARCH function.

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  Description STRING(MAX),
  DescriptionTokens TOKENLIST AS (TOKENIZE_FULLTEXT(Description)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(DescriptionTokens)
PARTITION BY SingerId;

INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 1, 'rock album');
INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 2, 'classical album');

The following query searches the column called Description for a token called classical. If this token is found for singer ID 1, the matching rows are returned.

SELECT a.AlbumId, a.Description
FROM Albums a
WHERE a.SingerId = 1 AND SEARCH(a.DescriptionTokens, 'classical');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 *---------------------------*/

The following query is like the previous one. However, if Description contains the classical or rock token, the matching rows are returned.

SELECT a.AlbumId, a.Description
FROM Albums a
WHERE a.SingerId = 1 AND SEARCH(a.DescriptionTokens, 'classical OR rock');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 | 1       | rock album      |
 *---------------------------*/

The following query is like the previous ones. However, if Description contains the classic and albums token, the matching rows are returned. When enhance_query is enabled, it includes similar matches of classical and album.

SELECT a.AlbumId, a.Description
FROM Albums a
WHERE a.SingerId = 1 AND SEARCH(a.DescriptionTokens, 'classic albums', enhance_query => TRUE);

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 *---------------------------*/

`SEARCH_NGRAMS`

SEARCH_NGRAMS(
  tokens,
  ngrams_query
  [, min_ngrams=>value]
  [, min_ngrams_percent=>value]
)

Description

Checks whether enough n-grams match the tokens in a fuzzy search.

Definitions

tokens: A TOKENLIST value that contains a list of n-grams. It must be a TOKENLIST generated by either TOKENIZE_SUBSTRING or TOKENIZE_NGRAMS.
ngrams_query: A STRING value that represents a fuzzy search query. This function generates ngram query terms from this value, using tokens's ngram_size_max as ngram size, or query as is if the query is shorter than ngram_size_max. Then the function looks for those ngrams in tokens. When tokens is generated by TOKENIZE_SUBSTRING, ngrams_query value is broken into words first, then n-grams are generated from the each word.
min_ngrams: A named argument with an INT64 value. The value specifies the minimum number of n-grams in ngrams_query that have to match in order for SEARCH_NGRAMS to return true. This only counts distinct n-grams and ignores repeating n-grams. The default value for this argument is 2.
min_ngrams_percent: A named argument with a FLOAT64 value. The value specifies the minimum percentage of n-grams in ngrams_query that have to match in order for SEARCH_NGRAMS to return true. This only counts distinct n-grams and ignores repeating n-grams.

Details

This function must reference a substring or n-grams TOKENLIST column in a table that is also indexed in a search index.
This function returns NULL when tokens or ngrams_query is NULL.
This function returns false if the length of ngrams_query is smaller than ngram_size_min of tokens.
This function can only be used in the WHERE clause of a SQL query.

Return type

BOOL

Examples

The following examples reference a table called Albums and a search index called AlbumsIndex.

The Albums table contains columns DescriptionSubstrTokens and DescriptionNgramsTokens which tokenize a Description column using TOKENIZE_SUBSTRING and TOKENIZE_NGRAMS, respectively. Finally, AlbumsIndex indexes DescriptionSubstrTokens and DescriptionNgramsTokens.

CREATE TABLE Albums (
  AlbumId INT64 NOT NULL,
  Description STRING(MAX),
  DescriptionSubstrTokens TOKENLIST AS
    (TOKENIZE_SUBSTRING(Description, ngram_size_min=>3, ngram_size_max=>3)) HIDDEN,
  DescriptionNgramsTokens TOKENLIST AS
    (TOKENIZE_NGRAMS(Description, ngram_size_min=>3, ngram_size_max=>3)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(DescriptionSubstrTokens, DescriptionNgramsTokens);

INSERT INTO Albums (AlbumId, Description) VALUES (1, 'rock album');
INSERT INTO Albums (AlbumId, Description) VALUES (2, 'classical album');
INSERT INTO Albums (AlbumId, Description) VALUES (3, 'last note');

The following query searches the column Description for clasic. The query is misspelled, so querying with SEARCH_SUBSTRING(a.DescriptionSubstrTokens, 'clasic') doesn't return a row, but the n-grams search is able to find similar matches.

SEARCH_NGRAMS first transforms the query clasic into n-grams of size 3 (the value of DescriptionSubstrTokens's ngram_size_max), producing ['asi', 'cla', 'las', 'sic']. Then it finds rows that have at least two of these n-grams (the default value for min_ngrams) in the DescriptionSubstrTokens column.

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  SEARCH_NGRAMS(a.DescriptionSubstrTokens, 'clasic');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 *---------------------------*/

If we change the min_ngrams to 1, then the query will also return the row with last which has one n-gram match with las. This example illustrates the decreased relevancy of the returned results when this parameter is set low.

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  SEARCH_NGRAMS(a.DescriptionSubstrTokens, 'clasic', min_ngrams=>1);

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 | 3       | last notes      |
 *---------------------------*/

The following query searches the column Description for clasic albun. As the DescriptionSubstrTokens is tokenized by TOKENIZE_SUBSTRING, the query is segmented into ['clasic', 'albun'] first, then n-gram tokens are generated from those words, producing the following: ['alb', 'asi', 'bun', 'cla', 'las', 'lbu', 'sic'].

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  SEARCH_NGRAMS(a.DescriptionSubstrTokens, 'clasic albun');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 | 1       | rock album      |
 *---------------------------*/

The following query searches the column Description for l al, but using the DescriptionNgramsTokens this time. As the DescriptionNgramsTokens is generated by TOKENIZE_NGRAMS, there is no splitting into words before making n-gram tokens, so the query n-gram tokens are generated as the following: ['%20al', 'l%20a'].

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  SEARCH_NGRAMS(a.DescriptionNgramsTokens, 'l al');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 *---------------------------*/

`SEARCH_SUBSTRING`

SEARCH_SUBSTRING(
  tokens,
  substring_query
  [, relative_search_type=>value]
)

Description

Returns TRUE if a substring query matches tokens.

Definitions

tokens: A TOKENLIST value that contains a list of substring tokens.
substring_query: A STRING value that represents a substring query.
relative_search_type: A named argument with a STRING value. The value refines the substring search result. To use a given relative_search_type, the substring TOKENLIST must have been generated with the corresponding type in its TOKENIZE_SUBSTRING relative_search_types argument. We support these relative search types:
- adjacent_and_in_order: The substring query tokens must appear adjacent to one another and in order in the value.
- value_prefix: The substring query must be found at the start of tokens.
- value_suffix: The substring query must be found at the end of tokens.
- word_prefix: The substring query must be found at the start of a word in tokens.
- word_suffix: The substring query must be found at the end of a word in tokens.

Details

Returns TRUE if tokens is a match for substring_query.
This function must reference a substring TOKENLIST column in a table that is also indexed in a search index. To add a substring TOKENLIST column to a table and to a search index, see the examples for this function.
This function returns NULL when tokens or substring_query is NULL.
This function can only be used in the WHERE clause of a SQL query.

Return type

BOOL

Examples

The following examples reference a table called Albums and a search index called AlbumsIndex.

The Albums table contains a column called DescriptionSubstrTokens, which tokenizes the input added to the Description column using TOKENIZE_SUBSTRING, and then saves those substring tokens in the DescriptionSubstrTokens column. Finally, AlbumsIndex indexes DescriptionSubstrTokens. Once DescriptionSubstrTokens is indexed, it can be used with the SEARCH_SUBSTRING function.

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  Description STRING(MAX),
  DescriptionSubstrTokens TOKENLIST AS (TOKENIZE_SUBSTRING(Description, support_relative_search=>TRUE)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(DescriptionSubstrTokens)
PARTITION BY SingerId;

INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 1, 'rock album');
INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 2, 'classical album');

The following query searches the column called Description for a token called ssic. If this token is found for singer ID 1, the matching rows are returned.

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  a.SingerId = 1 AND SEARCH_SUBSTRING(a.DescriptionSubstrTokens, 'ssic');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 *---------------------------*/

The following query searches the column called Description for a token called both lbu and oc. If these tokens are found for singer ID 1, the matching rows are returned.

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  a.SingerId = 1 AND SEARCH_SUBSTRING(a.DescriptionSubstrTokens, 'lbu oc');

/*-----------------------*
 | AlbumId | Description |
 +-----------------------+
 | 1       | rock album  |
 *-----------------------*/

The following query searches the column called Description for a token called al at the start of a word. If this token is found for singer ID 1, the matching rows are returned.

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  a.SingerId = 1 AND SEARCH_SUBSTRING(a.DescriptionSubstrTokens, 'al', relative_search_type=>'word_prefix');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 | 2       | classical album |
 | 1       | rock album      |
 *---------------------------*/

The following query searches the column called Description for a token called al at the start of tokens. If this token is found for singer ID 1, the matching rows are returned. Because there are no matches, no rows are returned.

SELECT
  a.AlbumId, a.Description
FROM
  Albums a
WHERE
  a.SingerId = 1 AND SEARCH_SUBSTRING(a.DescriptionSubstrTokens, 'al', relative_search_type=>'value_prefix');

/*---------------------------*
 | AlbumId | Description     |
 +---------------------------+
 |         |                 |
 *---------------------------*/

`SNIPPET`

SNIPPET(
  data_to_search,
  raw_search_query
  [, enhance_query=>{ TRUE | FALSE }]
  [, max_snippet_width=>value]
  [, max_snippets=>value]
  [, language_tag=>value]
  [, content_type=>value]
)

Description

Gets a list of snippets that match a full-text search query.

Definitions

data_to_search: A STRING value that represents the data to search over.
raw_search_query: A STRING value that represents the terms of a raw search query.
max_snippets: A named argument with an INT64 value. The value represents the maximum number of output snippets to produce.
max_snippet_width: A named argument with an INT64 value. The value represents the width of the output snippet. The width is measured by the estimated number of average proportional-width characters. For example, a wide character like 'M' will use up more space than a narrow character like 'i'.
enhance_query: A named argument with a BOOL value. The value determines whether to enhance the search query. For example, if enhance_query is performed on the search query containing a term classic, that query can expand to include similar terms such as classical or classics. The search query will not be enhanced if enhance_query call takes longer than the timeout.
- If TRUE, the search query will be enhanced to improve search quality.
- If FALSE (default), the search query will not be not enhanced.
language_tag: A named argument with a STRING value. The value contains an IETF BCP 47 language tag. You can use this tag to specify the language for raw_search_query. If the value for this argument is NULL, this function doesn't use a specific language. If this argument is not specified, NULL is used by default.
content_type: A named argument with a STRING value. The value represents the mime type of the content. Currently, "text/html" is supported for this function.

Details

Each snippet contains a matching substring of the data_to_search, and a list of highlights for the location of matching terms.

This function returns NULL when data_to_search or raw_search_query is NULL.

Return type

JSON

The JSON value has this format and definitions:

{
  "snippets":[
    {
      "highlights":[
        {
          "end_position": json_number,
          "start_position": json_number
        },
      ]
      "snippet": json_string
    }
  ]
}

snippets: A JSON object that contains snippets from data_to_search. These are snippets of text for raw_search_query from the provided data_to_search argument.
highlights: A JSON array that contains the position of each search term found in snippet.
begin: A JSON number that represents the position of a search term's first character in snippet.
end: A JSON number that represents the position of a search term's final character in snippet.
snippet: A JSON string that represents an individual snippet from snippets.

Examples

The following query produces a single snippet, Rock albums rock. with two highlighted positions for the matching raw search query term, rock:

SELECT SNIPPET('Rock albums rock.', 'rock') AS Snippet

/*---------------------------------------------------*
 | Snippet                                           |
 +---------------------------------------------------+
 | {"snippets":[                                     |
 |     {"highlights":[                               |
 |           {"begin":1, "end":5},                   |
 |           {"begin":13,"end":17}                   |
 |     ], "snippet":"Rock albums rock."}             |
 | ]}                                                |
 *---------------------------------------------------*/

`TOKEN`

TOKEN(value_to_tokenize)

Description

Constructs an exact match TOKENLIST value by tokenizing a BYTE or STRING value verbatim to accelerate exact match expressions in SQL.

Definitions

value_to_tokenize: A BYTE, ARRAY<BYTE>, STRING or ARRAY<STRING> value to tokenize for searching with exact match expressions.

Details

This function returns NULL when value_to_tokenize is NULL.

Return type

TOKENLIST

Examples

The Albums table contains a column called SingerNameToken and SongTitlesToken, which tokenizes the SingerName and SongTitles columns respectively using the TOKEN function. Finally, AlbumsIndex indexes SingerNameToken and SongTitlesToken, which makes it possible for Spanner to use the index to accelerate exact-match expressions in SQL.

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  SingerName STRING(MAX),
  SingerNameToken TOKENLIST AS (TOKEN(SingerName)) HIDDEN,
  SongTitles ARRAY<STRING(MAX)>,
  SongTitlesToken TOKENLIST AS (TOKEN(SongTitles)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(SingerNameToken, SongTitlesToken);

-- For example, the INSERT statement below generates SingerNameToken of
-- 'Catalina Smith', and SongTitlesToken of
-- ['Starting Again', 'The Second Title'].
INSERT INTO Albums (SingerId, AlbumId, SingerName, SongTitles)
  VALUES (1, 1, 'Catalina Smith', ['Starting Again', 'The Second Time']);

The following query finds the column SingerName is equal to Catalina Smith. The query optimizer could choose to accelerate the condition using AlbumsIndex with SingerNameToken. Optionally, the query can provide @{force_index = AlbumsIndex} to force the optimizer to use AlbumsIndex.

SELECT a.AlbumId
FROM Albums @{force_index = AlbumsIndex} a
WHERE a.SingerName = 'Catalina Smith';

/*---------*
 | AlbumId |
 +---------+
 | 1       |
 *---------*/

The following query is like the previous ones. However, this time the query searches for SongTitles that contain the string Starting Again. Array conditions should use ARRAY_INCLUDES, ARRAY_INCLUDES_ANY or ARRAY_INCLUDES_ALL functions to be eligible for using a search index for acceleration.

SELECT a.AlbumId
FROM Albums a
WHERE ARRAY_INCLUDES(a.SongTitles, 'Starting Again');

/*---------*
 | AlbumId |
 +---------+
 | 1       |
 *---------*/

`TOKENIZE_BOOL`

TOKENIZE_BOOL(value_to_tokenize)

Description

Constructs a boolean TOKENLIST value by tokenizing a BOOL value to accelerate boolean match expressions in SQL.

Definitions

value_to_tokenize: A BOOL or ARRAY<BOOL> value to tokenize for boolean match.

Details

This function returns NULL when value_to_tokenize is NULL.

Return type

TOKENLIST

Examples

The Albums table contains a column called IsAwardedToken, which tokenizes the IsAwarded column using TOKENIZE_BOOL function. Finally, AlbumsIndex indexes IsAwardedToken, which makes it possible for Spanner to use the index to accelerate boolean-match expressions in SQL.

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  IsAwarded BOOL,
  IsAwardedToken TOKENLIST AS (TOKENIZE_BOOL(IsAwarded)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(IsAwardedToken);

-- IsAwarded with TRUE generates IsAwardedToken with value 'y'.
INSERT INTO Albums (SingerId, AlbumId, IsAwarded) VALUES (1, 1, TRUE);

-- IsAwarded with FALSE generates IsAwardedToken with value 'n'.
INSERT INTO Albums (SingerId, AlbumId, IsAwarded) VALUES (1, 2, FALSE);

-- NULL IsAwarded generates IsAwardedToken with value NULL.
INSERT INTO Albums (SingerId, AlbumId) VALUES (1, 3);

The following query finds the column IsAwarded is equal to TRUE. The query optimizer could choose to accelerate the condition using AlbumsIndex with IsAwardedToken. Optionally, the query can provide @{force_index = AlbumsIndex} to force the optimizer to use AlbumsIndex.

SELECT a.AlbumId
FROM Albums @{force_index = AlbumsIndex} a
WHERE IsAwarded = TRUE;

`TOKENIZE_FULLTEXT`

TOKENIZE_FULLTEXT(
  value_to_tokenize
  [, language_tag=>value]
  [, content_type=>{ "text/plain" | "text/html" }]
)

Description

Constructs a full-text TOKENLIST value by tokenizing text for full-text matching.

Definitions

value_to_tokenize: A STRING or ARRAY<STRING> value to tokenize for full-text search.
language_tag: A named argument with a STRING value. The value contains an IETF BCP 47 language tag. You can use this tag to specify the language of value_to_tokenize. If the value for this argument is NULL, no specific language is used by this function. If this argument is not specified, NULL is used by default.
content_type: A named argument with a STRING value. Indicates the MIME type of value. This can be:
- "text/plain" (default): value contains plain text. All tokens are assigned to the small token category.
- "text/html": value contains HTML. The HTML tags are removed. HTML-escaped entities are replaced with their unescaped equivalents (for example, < becomes <). A token category is assigned to each token depending on its prominence in the HTML. For example, bolded text or text in a <h1> tag might have higher prominence than normal text and thus might be placed into a different token category.
  
  We use token categories during scoring to boost the weight of high-prominence tokens.

Details

This function returns NULL when value_to_tokenize is NULL.

Return type

TOKENLIST

Examples

In the following example, a TOKENLIST column is created using the TOKENIZE_FULLTEXT function:

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  Title STRING(MAX),
  Description STRING(MAX),
  DescriptionTokens TOKENLIST AS (TOKENIZE_FULLTEXT(Description)) HIDDEN,
  TitleTokens TOKENLIST AS (
    TOKENIZE_FULLTEXT(Title, token_category=>"title")) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

-- DescriptionTokens is generated from the Description value, using the
-- TOKENIZE_FULLTEXT function. For example, the following INSERT statement
-- generates DescriptionTokens with the tokens ['rock', 'album']. TitleTokens
-- will contain ['abbey', 'road'] and these tokens will be assigned to the
-- "title" token category.
INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 1, 'rock album');

-- Capitalization and delimiters are removed during tokenization. For example,
-- the following INSERT statement generates DescriptionTokens with the tokens
-- ['classical', 'albums'].
INSERT INTO Albums (SingerId, AlbumId, Description) VALUES (1, 1, 'Classical, Albums.');

To query a full-text TOKENLIST column, see the SEARCH function.

`TOKENIZE_NGRAMS`

TOKENIZE_NGRAMS(
  value_to_tokenize
  [, ngram_size_min=>value]
  [, ngram_size_max=>value]
  [, remove_diacritics=> { TRUE | FALSE }]
)

Description

Constructs an n-gram TOKENLIST value by tokenizing a STRING value for matching n-grams.

Definitions

value_to_tokenize: A STRING value to tokenize for the n-gram match.
ngram_size_min: A named argument with an INT64 value. The value represents the minimum length of n-gram tokens to generate. N-gram expressions won't be accelerated if the query token length is shorter than this value. The default value for this argument is 1.
ngram_size_max: A named argument with an INT64 value. The value represents the maximum size of n-gram tokens to generate. A larger value generates more tokens and may perform better on terms with the longer length. The default value for this argument is 4.
remove_diacritics: A named argument with a BOOL value. If TRUE, the diacritics is removed from value_to_tokenize before indexing. This is useful when you want to search a substring or ngram, regardless of diacritics. When a search query is called on a TOKENLIST value with remove_diacritics set as TRUE, the diacritics will also be removed at query time from the search queries.

Details

This function returns NULL when value_to_tokenize is NULL.

Return type

TOKENLIST

Examples

In the following example, a TOKENLIST column is created using the TOKENIZE_NGRAMS function. The INSERT generates a TOKENLIST which contains two sets of tokens. First, the whole string is broken up into n-grams with a length in the range [ngram_size_min, ngram_size_max-1]. Capitalization and whitespace are preserved in the n-grams. These n-grams are placed in the first position in the tokenlist.

[" ", " M", " Me", "vy ", "y ", "y M", H, He, Hea, Heav, ...], ...

Second, any n-grams with length equal to ngram_size_max are stored in sequence, with the first of these in the same position as the smaller n-grams. (In this example, the Heav token is in the first position.)

..., eavy, "avy ", "vy M", "y Me", " Met", Meta, etal

CREATE TABLE Albums (
  AlbumId INT64 NOT NULL,
  Description STRING(MAX),
  DescriptionNgramTokens TOKENLIST
    AS (TOKENIZE_NGRAMS(Description)) HIDDEN,
) PRIMARY KEY (AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(DescriptionNgramTokens);

INSERT INTO Albums (AlbumId, Description) VALUES (1, 'Heavy Metal');

To query an n-gram TOKENLIST column, see the SEARCH_NGRAMS function.

`TOKENIZE_NUMBER`

TOKENIZE_NUMBER(
  value_to_tokenize,
  [, comparison_type=> { "all" | "equality" }]
  [, algorithm=>{ "auto" | "logtree" | "prefixtree" | "floatingpoint" }]
  [, min=>value ]
  [, max=>value ]
  [, granularity=>value ]
  [, tree_base=>value ]
  [, precision=>value ]
)

Description

Constructs a numeric TOKENLIST value by tokenizing numeric values to accelerate numeric comparison expressions in SQL.

Definitions

value_to_tokenize: An INT64, ARRAY<INT64>, FLOAT64 or ARRAY<FLOAT64> value to tokenize for numeric comparison expressions.
comparison_type: A named argument with a STRING value. The value represents the type of comparison to use for numeric expressions. Set to equality to save space if equality is only required comparison. Default is all.
algorithm: A named argument with a STRING value. The value indicates the indexing algorithm to use. Supported algorithms are limited, depending on the type of value being indexed. The default value is auto.
- auto: Use a default algorithm based on the type of the value argument.
- logtree: Suitable for uniformly distributed integers.
- prefixtree: Suitable for exponentially distributed integers.
- floatingpoint: Suitable for FLOAT64 values that are fractional.
min: A named argument with the same type as value_to_tokenize. Values less than min are indexed in the same index bucket. This will not cause incorrect results, but may cause significant over-retrieval for queries with a range that includes values lesser than min. Don't use min when comparison_type is equality.
max: A named argument with the same type as value_to_tokenize. Values greater than max are indexed in the same index bucket. This will not cause incorrect results, but may cause significant over-retrieval for queries with a range that includes values greater than the max. Don't use max when comparison_type is equality.
granularity: A named argument with the same type as value_to_tokenize. The value represents the width of each indexing bucket. Values in the same bucket are indexed together, so larger buckets are more storage efficient, but may cause over-retrieval, causing high latency during query execution. granularity is only allowed when algorithm is logtree or prefixtree.
tree_base: A named argument with an INT64 value. The value is the numerical base of a tree for tree-based algorithms.

For example, the value of 2 means that each tree token represents some power-of-two number of buckets. In the case of a value indexed in the 1024th bucket, there is a token for [1024,1024], then a token for [1024,1025], then a token for [1024, 1027], and so on.

Increasing tree_base reduces the required number of index tokens and increases the required number of query tokens.

The default value is 2. tree_base is only allowed when algorithm is logtree or prefixtree.
precision: A named argument with an INT64 value. Reducing the precision reduces the number of index tokens, but increases over-retrieval when queries specify ranges with a high number of significant digits. The default value is 15. precision is only allowed when algorithm is floatingpoint.

Details

This function returns NULL when value_to_tokenize is NULL.

Return type

TOKENLIST

Examples

The Albums table contains a column called the RatingTokens, which tokenizes the Rating column using the TOKENIZE_NUMBER function. Finally, AlbumsIndex indexes RatingTokens, which makes it possible for Spanner to use the index to accelerate numeric comparison expressions in SQL.

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  Rating INT64,
  RatingTokens TOKENLIST AS (TOKENIZE_NUMBER(Rating)) HIDDEN,
  TrackRating ARRAY<INT64>,
  TrackRatingTokens TOKENLIST AS (TOKENIZE_NUMBER(TrackRating)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

CREATE SEARCH INDEX AlbumsIndex ON Albums(RatingTokens, TrackRatingTokens);

-- RatingTokens and TrackRatingTokens are generated from Rating and TrackRating
-- values, respectively, using the TOKENIZE_NUMBER function.
INSERT INTO Albums (SingerId, AlbumId, Rating, TrackRating) VALUES (1, 1, 2, [2, 3]);
INSERT INTO Albums (SingerId, AlbumId, Rating, TrackRating) VALUES (1, 2, 5, [3, 5]);

The following query finds rows in which the column Rating is equal to 5. The query optimizer might choose to accelerate the condition using AlbumsIndex with RatingTokens. Optionally, the query can provide @{force_index = AlbumsIndex} to force the optimizer to use AlbumsIndex.

SELECT a.AlbumId
FROM Albums @{force_index = AlbumsIndex} a
WHERE a.Rating = 5;

/*---------*
 | AlbumId |
 +---------+
 | 2       |
 *---------*/

The following query is like the previous one. However, the condition is on the array column of TrackRating this time. Array conditions should use ARRAY_INCLUDES, ARRAY_INCLUDES_ANY or ARRAY_INCLUDES_ALL functions to be eligible for using a search index for acceleration.

SELECT a.AlbumId
FROM Albums a
WHERE ARRAY_INCLUDES_ALL(a.TrackRating, [2, 3]);

/*---------*
 | AlbumId |
 +---------+
 | 1       |
 *---------*/

SELECT a.AlbumId
FROM Albums a
WHERE ARRAY_INCLUDES_ANY(a.TrackRating, [3, 4, 5]);

/*---------*
 | AlbumId |
 +---------+
 | 1       |
 | 2       |
 *---------*/

The following query is like the previous ones. However, the condition is range this time. This query can also be accelerated, as default comparison_type is all which covers both equality and range comparisons.

SELECT a.AlbumId
FROM Albums a
WHERE a.Rating >= 2;

/*---------*
 | AlbumId |
 +---------+
 | 1       |
 | 2       |
 *---------*/

`TOKENIZE_SUBSTRING`

TOKENIZE_SUBSTRING(
  value_to_tokenize
  [, ngram_size_min=>value]
  [, ngram_size_max=>value]
  [, relative_search_types=>value]
  [, content_type=> { "text/plain" | "text/html" }]
  [, remove_diacritics=> { TRUE | FALSE }]
)

Description

Constructs a substring TOKENLIST value, which tokenizes text for substring matching.

Definitions

value_to_tokenize: A STRING or ARRAY<STRING> value to tokenize for a substring search.
ngram_size_min: A named argument with an INT64 value. The value is the minimum length of each substring token to generate. SEARCH_SUBSTRING is not able to match substring queries on the TOKENLIST if a substring query token length is shorter than this value. The default value for this argument is 1.
ngram_size_max: A named argument with an INT64 value. The value is the maximum size of each substring token to generate. A larger value generates more tokens. Searches are faster when the length of the search substring is greater than ngram_size_max. The default value for this argument is 4.
relative_search_types: A named argument with an ARRAY<STRING> value. The value determines which TOKENIZE_SUBSTRING relative search types are supported. See SEARCH_SUBSTRING for a list of the different relative search types.

In addition to the relative search types from SEARCH_SUBSTRING, TOKENIZE_SUBSTRING accepts a special flag, all, which means that all relative search types are supported.

If this argument is not used, then no relative search tokens are generated for the resulting TOKENLIST value.

Setting this value causes extra tokens to be generated to enable relative searches. A given relative search type can only be used in a query if that type, or all, is present in the relative_search_types argument. By default, relative_search_types is empty.
content_type: A named argument with a STRING value. Indicates the MIME type of value. This can be:
- "text/plain" (default): value contains plain text. All tokens are assigned to the small token category.
- "text/html": value contains HTML. The HTML tags are removed. HTML-escaped entities are replaced with their unescaped equivalents (for example, < becomes <). A token category is assigned to each token depending on its prominence in the HTML. For example, bolded text or text in a <h1> tag might have higher prominence than normal text and thus might be placed into a different token category.
  
  We use token categories during scoring to boost the weight of high-prominence tokens.
remove_diacritics: A named argument with a BOOL value. If TRUE, the diacritics is removed from value_to_tokenize before indexing. This is useful when you want to search a substring or ngram, regardless of diacritics. When a search query is called on a TOKENLIST value with remove_diacritics set as TRUE, the diacritics will also be removed at query time from the search queries.

Details

This function returns NULL when value_to_tokenize is NULL.

Return type

TOKENLIST

Example

In the following example, a TOKENLIST column is created using the TOKENIZE_SUBSTRING function. The INSERT generates a TOKENLIST which contains two sets of tokens. First, each word is broken up into n-grams with a length in the range [ngram_size_min, ngram_size_max-1], and any whole words with a length shorter than that ngram_size_max. All of these tokens are placed in the first position in the tokenlist.

[a, al, av, avy, e, ea, eav, et, eta, h, he, hea, ...], ...

Second, any n-grams with length equal to ngram_size_max are stored in subsequent positions. These tokens are used when searching for words larger than the maximum ngram size.

..., heav, eavy, <gap(1)>, meta, etal

CREATE TABLE Albums (
  SingerId INT64 NOT NULL,
  AlbumId INT64 NOT NULL,
  Description STRING(MAX),
  DescriptionSubstrTokens TOKENLIST
    AS (TOKENIZE_SUBSTRING(Description, ngram_size_min=>1, ngram_size_max=>4)) HIDDEN,
) PRIMARY KEY (SingerId, AlbumId);

INSERT INTO Albums (SingerId, AlbumId, Description)
  VALUES (1, 1, 'Heavy Metal');

To query a substring TOKENLIST column, see the SEARCH_SUBSTRING or SEARCH_NGRAMS function.

Search functions in GoogleSQL

Categories

Function list

SCORE

SCORE_NGRAMS

SEARCH

SEARCH_NGRAMS

SEARCH_SUBSTRING

SNIPPET

TOKEN

TOKENIZE_BOOL

TOKENIZE_FULLTEXT

TOKENIZE_NGRAMS

TOKENIZE_NUMBER

TOKENIZE_SUBSTRING

`SCORE`

`SCORE_NGRAMS`

`SEARCH`

`SEARCH_NGRAMS`

`SEARCH_SUBSTRING`

`SNIPPET`

`TOKEN`

`TOKENIZE_BOOL`

`TOKENIZE_FULLTEXT`

`TOKENIZE_NGRAMS`

`TOKENIZE_NUMBER`

`TOKENIZE_SUBSTRING`