Search functions

SEARCH(
  data_to_search, search_query
  [, json_scope => { 'JSON_VALUES' | 'JSON_KEYS' | 'JSON_KEYS_AND_VALUES' } ]
  [, analyzer => { 'LOG_ANALYZER' | 'NO_OP_ANALYZER' | 'PATTERN_ANALYZER'} ]
  [, analyzer_options => analyzer_options_values ]
)

SELECT
  -- ERROR: `search_query` is NULL.
  SEARCH('foobarexample', NULL) AS a,

  -- ERROR: `search_query` contains no tokens.
  SEARCH('foobarexample', '') AS b,

SELECT
  -- TRUE: '-' and ' ' are delimiters.
  SEARCH('foobar-example', 'foobar example') AS a,

  -- TRUE: The search query is a constant expression evaluated to 'foobar'.
  SEARCH('foobar-example', CONCAT('foo', 'bar')) AS b,

  -- FALSE: The search_query is not split.
  SEARCH('foobar-example', 'foobarexample') AS c,

  -- TRUE: The double backslash escapes the ampersand which is a delimiter.
  SEARCH('foobar-example', 'foobar\\&example') AS d,

  -- TRUE: The single backslash escapes the ampersand in a raw string.
  SEARCH('foobar-example', R'foobar\&example')AS e,

  -- FALSE: The backticks indicate that there must be an exact match for
  -- foobar&example.
  SEARCH('foobar-example', '`foobar&example`') AS f,

  -- TRUE: An exact match is found.
  SEARCH('foobar&example', '`foobar&example`') AS g

/*-------+-------+-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     | f     | g     |
 +-------+-------+-------+-------+-------+-------+-------+
 | true  | true  | false | true  | true  | false | true  |
 *-------+-------+-------+-------+-------+-------+-------*/

SELECT
  -- TRUE: The order of terms doesn't matter.
  SEARCH('foobar-example', 'example foobar') AS a,

  -- TRUE: Tokens are made lower-case.
  SEARCH('foobar-example', 'Foobar Example') AS b,

  -- TRUE: An exact match is found.
  SEARCH('foobar-example', '`foobar-example`') AS c,

  -- FALSE: Backticks preserve capitalization.
  SEARCH('foobar-example', '`Foobar`') AS d,

  -- FALSE: Backticks don't have special meaning for search_data and are
  -- not delimiters in the default LOG_ANALYZER.
  SEARCH('`foobar-example`', '`foobar-example`') AS e,

  -- TRUE: An exact match is found after the delimiter in search_data.
  SEARCH('foobar@example.com', '`example.com`') AS f,

  -- TRUE: An exact match is found between the space delimiters.
  SEARCH('a foobar-example b', '`foobar-example`') AS g;

/*-------+-------+-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     | f     | g     |
 +-------+-------+-------+-------+-------+-------+-------+
 | true  | true  | true  | false | false | true  | true  |
 *-------+-------+-------+-------+-------+-------+-------*/

SELECT
  -- FALSE: No single array entry matches all search terms.
  SEARCH(['foobar', 'example'], 'foobar example') AS a,

  -- FALSE: The search query is equivalent to foobar\\=.
  SEARCH('foobar=', '`foobar\\=`') AS b,

  -- FALSE: This is equivalent to the previous example.
  SEARCH('foobar=', R'`\foobar=`') AS c,

  -- TRUE: The equals sign is a delimiter in the data and query.
  SEARCH('foobar=', 'foobar\\=') AS d,

  -- TRUE: This is equivalent to the previous example.
  SEARCH('foobar=', R'foobar\=') AS e,

  -- TRUE: An exact match is found.
  SEARCH('foobar.example', '`foobar`') AS f,

  -- FALSE: `foobar.\` is not analyzed because of backticks; it is not
  -- followed by a delimiter in search_data 'foobar.example'.
  SEARCH('foobar.example', '`foobar.\`') AS g,

  -- TRUE: `foobar.` is not analyzed because of backticks; it is
  -- followed by the delimiter '.' in search_data 'foobar..example'.
  SEARCH('foobar..example', '`foobar.`') AS h;

/*-------+-------+-------+-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     | f     | g     | h     |
 +-------+-------+-------+-------+-------+-------+-------+-------+
 | false | false | false | true  | true  | true  | false | true  |
 *-------+-------+-------+-------+-------+-------+-------+-------*/

SELECT
  -- TRUE: The phrase `foo bar` is in `foo bar baz`.
  -- The tokens in `data_to_search` are `foo`, `bar`, and `baz`.
  -- The searchable tokens in `query_string` are `foo` and `bar`
  -- and because they appear in that exact order in `data_to_search`,
  -- the function returns TRUE.
  SEARCH(R'foo bar baz', R'"foo bar"') AS a,

  -- TRUE: Case is ignored.
  -- The tokens in `data_to_search` are `foo`, `bar`, and `baz`.
  -- The searchable tokens in `query_string` are `foo` and `bar`
  -- and because they appear in that exact order in `data_to_search`,
  -- the function return TRUE.
  SEARCH(R'Foo bar baz', R'"foo Bar"') AS b,

  -- TRUE: Both `-` and `&` are delimiters used during tokenization.
  -- The tokens in `data_to_search` are `foo`, `bar`, and `baz`.
  -- The searchable tokens in `query_string` are `foo` and `bar`
  -- and because they appear in that exact order in `data_to_search`,
  -- the function returns TRUE.
  SEARCH(R'foo-bar baz', R'"foo&bar"') AS c,

  -- FALSE: Backticks in a phrase are treated as normal characters.
  -- The tokens in `data_to_search` are `foo`, `bar`, and `baz`.
  -- The searchable tokens in `query_string` are:
  -- `foo
  -- bar`
  -- Because these searchable tokens don't appear in `data_to_search`,
  -- the function returns FALSE.
  SEARCH(R'foo bar baz', R'"`foo bar`"') AS d,

  -- FALSE: `foo bar` is not in `foo else bar`.
  -- The tokens in `data_to_search` are `foo`, `else`, and `bar`.
  -- The searchable tokens in `query_string` are `foo` and `bar`.
  -- Even though they appear in `data_to_search`, but because they
  -- do not appear in that exact order (`foo` before `bar`),
  -- the function returns FALSE.
  SEARCH(R'foo else bar', R'"foo bar"') AS e,

  -- FALSE: `foo baz` is not in `foo bar baz`.
  -- The `search_query` produces two terms. The first term is `bar`, which
  -- matches with the similar token in `data_to_search`. However, the second
  -- term is the phrase "foo&baz" with two tokens, `foo` and `baz`. Because
  -- `foo` and `baz` do not appear next to each other in `data_to_search`
  -- (`bar` is in between), the function returns FALSE.
  SEARCH(R'foo-bar-baz', R'bar "foo&baz"') AS f;

/*-------+-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     | f     |
 +-------+-------+-------+-------+-------+-------+
 | true  | true  | false | false | false | false |
 *-------+-------+-------+-------+-------+-------*/

SELECT
  -- FALSE: Only double quotes need to be escaped in a phrase.
  -- The tokens in `data_to_search` are `foo`, `bar`, and `baz`.
  -- The searchable tokens in `query_string` are `foo\` and `bar` and they
  -- must appear in that exact order in `data_to_search`, but don't.
  SEARCH(
    R'foo bar baz',
    R'"foo\ bar"',
    analyzer_options=>'{"delimiters": [" "]}') AS a,

  -- TRUE: `foo bar` is in `foo bar baz` after tokenization with the given
  -- delimiters.
  -- The tokens in `data_to_search` are `foo`, `bar`, and `baz`.
  -- The searchable tokens in `query_string` are `foo` and `bar` and they
  -- must appear in that exact order in `data_to_search`.
  SEARCH(
    R'foo bar baz',
    R'"foo? bar"',
    analyzer_options=>'{"delimiters": [" ", "?"]}') AS b,

  -- TRUE: `read book` is in `read book now` after `the` is ignored.
  -- The tokens in `data_to_search` are `read`, `book`, and `now`.
  -- The searchable tokens in `query_string` are `read` and `book` and they
  -- must appear in that exact order in `data_to_search`.
  SEARCH(
    'read the book now',
    R'"read the book"',
    analyzer_options => '{ "token_filters": [{"stop_words": ["the"]}] }') AS c,

  -- FALSE: `c d` is not in `a`, `b`, `cd`, `e` or `f` after tokenization with
  -- the given pattern.
  -- The tokens in `data_to_search` are `a`, `b`, `cd`, `e` and `f`.
  -- The searchable tokens in `query_string` are `c` and `d` and they
  -- must appear in that exact order in `data_to_search`. `data_to_search`
  -- contains a `cd` token, but not a `c` or `d` token.
  SEARCH(
    R'abcdef',
    R'"c d"',
    analyzer=>'PATTERN_ANALYZER',
    analyzer_options=>'{"patterns": ["(?:cd)|[a-z]"]}') AS d,

  -- TRUE: `ant apple` is in `ant apple avocado` after tokenization with
  -- the given pattern.
  -- The tokens in `data_to_search` are `ant`, `apple`, and `avocado`.
  -- The searchable tokens in `query_string` are `ant` and `apple` and they
  -- must appear in that exact order in `data_to_search`.
  SEARCH(
    R'ant orange apple avocado',
    R'"ant apple"',
    analyzer=>'PATTERN_ANALYZER',
    analyzer_options=>'{"patterns": ["a[a-z]"]}') AS e;

/*-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     |
 +-------+-------+-------+-------+-------+
 | false | true  | true  | false | true  |
 *-------+-------+-------+-------+-------*/

SELECT
  -- TRUE: exact match
  SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER') AS a,

  -- FALSE: Backticks are not special characters for `NO_OP_ANALYZER`.
  SEARCH('foobar', '\`foobar\`', analyzer=>'NO_OP_ANALYZER') AS b,

  -- FALSE: The capitalization does not match.
  SEARCH('foobar', 'Foobar', analyzer=>'NO_OP_ANALYZER') AS c,

  -- FALSE: There are no delimiters for `NO_OP_ANALYZER`.
  SEARCH('foobar example', 'foobar', analyzer=>'NO_OP_ANALYZER') AS d,

  -- TRUE: An exact match is found.
  SEARCH('', '', analyzer=>'NO_OP_ANALYZER') AS e,

  -- FALSE: 'foo bar' and "foo bar" are not considered an exact match.
  SEARCH( R'foo bar baz', R'"foo bar"', analyzer=>'NO_OP_ANALYZER') AS f,

  -- TRUE: "foo bar" and "foo Bar" are considered an exact match because the
  -- analysis is case-insensitive.
  SEARCH( R'"foo bar"', R'"foo Bar"', analyzer=>'NO_OP_ANALYZER') AS g;

/*-------+-------+-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     | f     | g     |
 +-------+-------+-------+-------+-------+-------+-------+
 | true  | false | false | false | true  | false | true  |
 *-------+-------+-------+-------+-------+-------+-------*/

/*-------------------+-------------------------+------------------*
 | breakfast         | lunch                   | dinner           |
 +-------------------+-------------------------+------------------+
 | Potato pancakes   | Toasted cheese sandwich | Beef soup        |
 | Avocado toast     | Tomato soup             | Chicken soup     |
 *-------------------+-------------------------+------------------*/

WITH
  meals AS (
    SELECT
      'Potato pancakes' AS breakfast,
      'Toasted cheese sandwich' AS lunch,
      'Beef soup' AS dinner
    UNION ALL
    SELECT
      'Avocado toast' AS breakfast,
      'Tomato soup' AS lunch,
      'Chicken soup' AS dinner
  )
SELECT
  SEARCH(lunch, 'soup') AS lunch_soup,
  SEARCH((breakfast, dinner), 'soup') AS breakfast_or_dinner_soup,
  SEARCH(meals, 'soup') AS anytime_soup
FROM meals;

/*------------+--------------------------+--------------*
 | lunch_soup | breakfast_or_dinner_soup | anytime_soup |
 +------------+--------------------------+--------------+
 | false      | true                     | true         |
 | true       | true                     | true         |
 *------------+--------------------------+--------------*/

WITH data AS ( SELECT 'Please use foobar@example.com as your email.' AS email )
SELECT
  SEARCH(email, 'exam') AS a,
  SEARCH(email, 'foobar') AS b,
  SEARCH(email, 'example.com') AS c,
  SEARCH(email, R'"please use"') AS d,
  SEARCH(email, R'"as email"') AS e
FROM data;

/*-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     |
 +-------+-------+-------+-------+-------+
 | false | true  | true  | true  | false |
 *-------+-------+-------+-------+-------*/

WITH data AS ( SELECT 'Please use foobar@example.com as your email.' AS email )
SELECT
  SEARCH(email, 'foobar', analyzer_options=>'{"delimiters": [" ", "@"]}') AS a,
  SEARCH(email, 'example', analyzer_options=>'{"delimiters": [" ", "@"]}') AS b,
  SEARCH(email, 'example.com', analyzer_options=>'{"delimiters": [" ", "@"]}') AS c,
  SEARCH(email, 'foobar@example.com', analyzer_options=>'{"delimiters": [" ", "@"]}') AS d,
  SEARCH(email, R'use "foobar example.com" "as your"', analyzer_options=>'{"delimiters": [" ", "@"]}') AS e
FROM data;

/*-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     |
 +-------+-------+-------+-------+-------+
 | true  | false | true  | true  | true  |
 *-------+-------+-------+-------+-------*/

WITH meals AS ( SELECT 'Tomato soup' AS lunch )
SELECT
  SEARCH(lunch, 'Tomato soup', analyzer=>'NO_OP_ANALYZER') AS a,
  SEARCH(lunch, 'soup', analyzer=>'NO_OP_ANALYZER') AS b,
  SEARCH(lunch, 'tomato soup', analyzer=>'NO_OP_ANALYZER') AS c,
  SEARCH(lunch, R'"Tomato soup"', analyzer=>'NO_OP_ANALYZER') AS d
FROM meals;

/*-------+-------+-------+-------*
 | a     | b     | c     | d     |
 +-------+-------+-------+-------+
 | true  | false | false | false |
 *-------+-------+-------+-------*/

WITH data AS ( SELECT 'Please use foobar@example.com as your email.' AS email )
SELECT
  SEARCH(email, 'exam', analyzer=>'PATTERN_ANALYZER') AS a,
  SEARCH(email, 'foobar', analyzer=>'PATTERN_ANALYZER') AS b,
  SEARCH(email, 'example.com', analyzer=>'PATTERN_ANALYZER') AS c,
  SEARCH(email, R'foobar "EXAMPLE.com as" email', analyzer=>'PATTERN_ANALYZER') AS d
FROM data;

/*-------+-------+-------+-------*
 | a     | b     | c     | d     |
 +-------+-------+-------+-------+
 | false | true  | true  | true  |
 *-------+-------+-------+-------*/

WITH data AS ( SELECT 'Please use foobar@EXAMPLE.com as your email.' AS email )
SELECT
  SEARCH(email, 'EXAMPLE', analyzer=>'PATTERN_ANALYZER', analyzer_options=>'{"patterns": ["[A-Z]*"]}') AS a,
  SEARCH(email, 'example', analyzer=>'PATTERN_ANALYZER', analyzer_options=>'{"patterns": ["[a-z]*"]}') AS b,
  SEARCH(email, 'example.com', analyzer=>'PATTERN_ANALYZER', analyzer_options=>'{"patterns": ["[a-z]*"]}') AS c,
  SEARCH(email, 'example.com', analyzer=>'PATTERN_ANALYZER', analyzer_options=>'{"patterns": ["[a-zA-Z.]*"]}') AS d
FROM data;

/*-------+-------+-------+-------+-------*
 | a     | b     | c     | d     | e     |
 +-------+-------+-------+-------+-------+
 | true  | false | false | true  | false |
 *-------+-------+-------+-------+-------*/

VECTOR_SEARCH(
  {TABLE base_table | base_table_query_statement},
  column_to_search,
  TABLE query_table
  [, query_column_to_search => query_column_to_search_value]
  [, top_k => top_k_value ]
  [, distance_type => distance_type_value ]
  [, options => options_value ]
)

VECTOR_SEARCH(
  { TABLE base_table | base_table_query_statement },
  column_to_search,
  (query_statement)
  [, [ query_column_to_search => ] query_column_to_search_value]
  [, top_k => top_k_value ]
  [, distance_type => distance_type_value ]
  [, options => options_value ]
)

CREATE OR REPLACE TABLE mydataset.table1
(
  id INT64,
  my_embedding ARRAY<FLOAT64>
);

INSERT mydataset.table1 (id, my_embedding)
VALUES(1, [1.0, 2.0]),
(2, [2.0, 4.0]),
(3, [1.5, 7.0]),
(4, [1.0, 3.2]),
(5, [5.0, 5.4]),
(6, [3.7, 1.8]),
(7, [4.4, 2.9]);

CREATE OR REPLACE TABLE mydataset.table2
(
  query_id STRING,
  embedding ARRAY<FLOAT64>
);

INSERT mydataset.table2 (query_id, embedding)
VALUES('dog', [1.0, 2.0]),
('cat', [3.0, 5.2]);

SELECT *
FROM
  VECTOR_SEARCH(
    TABLE mydataset.table1,
    'my_embedding',
    (SELECT query_id, embedding FROM mydataset.table2),
    'embedding',
    top_k => 2);

/*----------------+-----------------+---------+----------------------------------------*
 | query.query_id | query.embedding | base.id | base.my_embedding | distance           |
 +----------------+-----------------+---------+-------------------+--------------------+
 | dog            | 1.0             | 1       | 1.0               | 0                  |
 |                | 2.0             |         | 2.0               |                    |
 +----------------+-----------------+---------+-------------------+--------------------+
 | dog            | 1.0             | 4       | 1.0               | 1.2000000000000002 |
 |                | 2.0             |         | 3.2               |                    |
 +----------------+-----------------+---------+-------------------+--------------------+
 | cat            | 3.0             | 2       | 2.0               | 1.5620499351813311 |
 |                | 5.2             |         | 4.0               |                    |
 +----------------+-----------------+---------+-------------------+--------------------+
 | cat            | 3.0             | 5       | 5.0               | 2.0099751242241779 |
 |                | 5.2             |         | 5.4               |                    |
 *----------------+-----------------+---------+-------------------+--------------------*/

SELECT *
FROM
  VECTOR_SEARCH(
    (SELECT * FROM mydataset.table1 WHERE id != 4),
    'my_embedding',
    (SELECT query_id, embedding FROM mydataset.table2),
    'embedding',
    top_k => 2,
    options => '{"use_brute_force":true}');

/*----------------+-----------------+---------+----------------------------------------*
 | query.query_id | query.embedding | base.id | base.my_embedding | distance           |
 +----------------+-----------------+---------+-------------------+--------------------+
 | dog            | 1.0             | 1       | 1.0               | 0                  |
 |                | 2.0             |         | 2.0               |                    |
 +----------------+-----------------+---------+-------------------+--------------------+
 | dog            | 1.0             | 2       | 2.0               | 2.23606797749979   |
 |                | 2.0             |         | 4.0               |                    |
 +----------------+-----------------+---------+-------------------+--------------------+
 | cat            | 3.0             | 2       | 2.0               | 1.5620499351813311 |
 |                | 5.2             |         | 4.0               |                    |
 +----------------+-----------------+---------+-------------------+--------------------+
 | cat            | 3.0             | 5       | 5.0               | 2.0099751242241779 |
 |                | 5.2             |         | 5.4               |                    |
 *----------------+-----------------+---------+-------------------+--------------------*/

SELECT *
FROM
  VECTOR_SEARCH(
    TABLE mydataset.table1,
    'my_embedding',
    TABLE mydataset.table2,
    'embedding',
    top_k => 2,
    distance_type => 'COSINE');

/*----------------+-----------------+---------+-------------------------------------------+
 | query.query_id | query.embedding | base.id | base.my_embedding | distance              |
 +----------------+-----------------+---------+-------------------+-----------------------+
 | dog            | 1.0             | 2       | 2.0               | 0                     |
 |                | 2.0             |         | 4.0               |                       |
 +----------------+-----------------+---------+-------------------+-----------------------+
 | dog            | 1.0             | 1       | 1.0               | 0                     |
 |                | 2.0             |         | 2.0               |                       |
 +----------------+-----------------+---------+-------------------+-----------------------+
 | cat            | 3.0             | 2       | 2.0               | 0.0017773842088002478 |
 |                | 5.2             |         | 4.0               |                       |
 +----------------+-----------------+---------+-------------------+-----------------------+
 | cat            | 3.0             | 1       | 1.0               | 0.0017773842088002478 |
 |                | 5.2             |         | 2.0               |                       |
 *----------------+-----------------+---------+-------------------+-----------------------*/