# SEARCH function and search indexes in BigQuery

> SEARCH checks whether every term of a query appears as a token in your text or JSON. It works on any table, and a search index lets it skip almost all of the data. We built an index on a 12 GB table and measured the bytes, plus the escaping rules that trip people.

Source: https://docs.querylab.io/bigquery/search/

Updated: 2026-10-06

`SEARCH(data, 'query')` returns TRUE when every term in the query appears as a whole token in the data. It splits text at delimiters, lowercases it, and compares tokens, so `SEARCH('Error: timeout', 'TIMEOUT')` is TRUE and `SEARCH('Error: timeout', 'time')` is FALSE. It works on any table, but it only gets cheap with a search index: on a 12 GB table, a query for one rare token read 127 MB with the index, against an upper bound of 11.7 GB without it. It is for point lookups, such as finding the log lines that mention one request ID.

## How a SEARCH query is matched

The default `LOG_ANALYZER` lowercases the text and cuts it at delimiters. The delimiters include space, `. , : ; = @ - _ / \ ( ) [ ] { } < > | ! ? + * & ' " $ %` and a few URL-encoded forms. A term matches only a whole token:

**whole-token matching on a log line**

```sql
SELECT * FROM (
  SELECT 'TIMEOUT' AS term, SEARCH('Error: timeout on host-42', 'TIMEOUT') AS matches
  UNION ALL
  SELECT 'time' AS term, SEARCH('Error: timeout on host-42', 'time') AS matches
  UNION ALL
  SELECT 'host' AS term, SEARCH('Error: timeout on host-42', 'host') AS matches
  UNION ALL
  SELECT '42' AS term, SEARCH('Error: timeout on host-42', '42') AS matches
  UNION ALL
  SELECT 'timeout host' AS term, SEARCH('Error: timeout on host-42', 'timeout host') AS matches
)
ORDER BY term
```

Result in BigQuery (5 rows):

| term | matches |
| --- | --- |
| 42 | true |
| TIMEOUT | true |
| host | true |
| time | false |
| timeout host | true |

`TIMEOUT` matches because case is folded. `time` doesn't match `timeout`, `host` matches `host-42` because `-` is a delimiter, and a query of several terms needs all of them. Since `_` and `.` are delimiters too, `user_id_42` and `user.id` match the terms `user` and `id`, but camel case is not split:

**_ and . split tokens, camel case does not**

```sql
SELECT value, SEARCH(value, 'id') AS finds_id, SEARCH(value, 'user') AS finds_user
FROM UNNEST(['user_id_42', 'userid42', 'user.id', 'userId']) AS value
ORDER BY value
```

Result in BigQuery (4 rows):

| value | finds_id | finds_user |
| --- | --- | --- |
| user.id | true | true |
| userId | false | false |
| user_id_42 | true | true |
| userid42 | false | false |

`SEARCH` is not a substring or pattern match. If you need `LIKE '%abc%'` behavior, `SEARCH` will not give it to you, and Google's intro lists fuzzy search, typo correction and wildcards as unsupported.

## The query language

A query is terms joined with `AND`, `OR` and parentheses, and a space between terms is an implicit `AND`. Double quotes make a phrase, which must appear in that order as adjacent tokens:

**AND, OR, grouping and phrases**

```sql
SELECT * FROM (
  SELECT 'error timeout' AS query, SEARCH('Error: timeout on host-42', 'error timeout') AS matches
  UNION ALL
  SELECT 'error AND nope' AS query, SEARCH('Error: timeout on host-42', 'error AND nope') AS matches
  UNION ALL
  SELECT 'error OR nope' AS query, SEARCH('Error: timeout on host-42', 'error OR nope') AS matches
  UNION ALL
  SELECT 'error (nope OR host)' AS query, SEARCH('Error: timeout on host-42', 'error (nope OR host)') AS matches
  UNION ALL
  SELECT '"timeout on"' AS query, SEARCH('Error: timeout on host-42', '"timeout on"') AS matches
  UNION ALL
  SELECT '"on timeout"' AS query, SEARCH('Error: timeout on host-42', '"on timeout"') AS matches
)
ORDER BY query
```

Result in BigQuery (6 rows):

| query | matches |
| --- | --- |
| "on timeout" | false |
| "timeout on" | true |
| error (nope OR host) | true |
| error AND nope | false |
| error OR nope | true |
| error timeout | true |

### Reserved characters and a hyphen trap

The query string has its own grammar, and the hyphen is part of it. Searching for an ID with a hyphen fails:

**the query host-42**

```sql
SELECT SEARCH('Error: timeout on host-42', 'host-42') AS matches
```

BigQuery rejects this query:

```text
Search query parser error: error at position 4: token recognition error at: '-'
```

Escape reserved characters with a double backslash in a normal string (`'host\\-42'`), a single backslash in a raw string (`r'host\-42'`), or put the term in backticks or double quotes. The reserved characters are `[ ] < > ( ) { } | ! ' " * & ? + / : = - \ ~ ^`. So `'trace_id=abc'` is an error as well.

**four ways to search for host-42**

```sql
SELECT * FROM (
  SELECT 'backslash in a normal string' AS how, SEARCH('Error: timeout on host-42', 'host\\-42') AS matches
  UNION ALL
  SELECT 'raw string', SEARCH('Error: timeout on host-42', r'host\-42')
  UNION ALL
  SELECT 'backticks', SEARCH('Error: timeout on host-42', '`host-42`')
  UNION ALL
  SELECT 'double quotes (phrase)', SEARCH('Error: timeout on host-42', '"host-42"')
)
ORDER BY how
```

Result in BigQuery (4 rows):

| how | matches |
| --- | --- |
| backslash in a normal string | true |
| backticks | true |
| double quotes (phrase) | true |
| raw string | true |

Backticks force an exact, case-sensitive match of the text inside, which has to start and end at delimiters in the data. The words `AND`, `OR`, `NOT`, `IN` and `NEAR` are reserved and need backticks:

**searching for the word AND**

```sql
SELECT SEARCH('retry AND fail', 'AND') AS matches
```

BigQuery rejects this query:

```text
Search query parser error: Search query contains the reserved keyword 'AND'.
```

### Constants only, and no empty queries

The query must be a constant expression. We tried to take it from a column with `UNNEST` and got "must be a constant expression", so you can't join a table of search terms to a table of text. A `NULL` query, an empty string, or a query that analyzes to no tokens is an error, not a FALSE:

**a NULL query**

```sql
SELECT SEARCH('abc', CAST(NULL AS STRING)) AS matches
```

BigQuery rejects this query:

```text
The 2nd argument of SEARCH() must not be null
```

**a query that is only punctuation**

```sql
SELECT SEARCH('abc', '...') AS matches
```

BigQuery rejects this query:

```text
Search query '...' results in 0 tokens.
```

## What can be searched

`SEARCH` accepts `STRING`, `ARRAY<STRING>`, `STRUCT`, `ARRAY<STRUCT>` and `JSON`. For any other type it returns FALSE without an error, which is easy to miss:

**the same 42 in four types**

```sql
SELECT label, matches FROM (
  SELECT 'STRING' AS label, SEARCH('order 42', '42') AS matches
  UNION ALL SELECT 'INT64', SEARCH(42, '42')
  UNION ALL SELECT 'ARRAY<STRING>', SEARCH(['order', '42'], '42')
  UNION ALL SELECT 'STRUCT', SEARCH(STRUCT('order' AS a, '42' AS b), '42')
)
ORDER BY label
```

Result in BigQuery (4 rows):

| label | matches |
| --- | --- |
| ARRAY<STRING> | true |
| INT64 | false |
| STRING | true |
| STRUCT | true |

You can pass a whole table row, and `SEARCH(t, 'printer')` looks at every searchable column of `t`:

**searching all columns of a row**

```sql
WITH tickets AS (
  SELECT 1 AS id, 'Ana' AS owner, 'printer offline' AS title
  UNION ALL SELECT 2, 'Boris', 'VPN timeout'
  UNION ALL SELECT 3, 'Printer Corp', 'invoice question'
)
SELECT id FROM tickets WHERE SEARCH(tickets, 'printer') ORDER BY id
```

Result in BigQuery (2 rows):

| id |
| --- |
| 1 |
| 3 |

For `JSON`, `json_scope` chooses what is searched: `JSON_VALUES` (default), `JSON_KEYS` or `JSON_KEYS_AND_VALUES`:

**keys and values of one JSON document**

```sql
WITH doc AS (SELECT JSON '{"status": "active", "tags": ["beta"]}' AS j)
SELECT * FROM (
  SELECT 'JSON_VALUES' AS scope, 'status' AS term, SEARCH(j, 'status', json_scope => 'JSON_VALUES') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_VALUES' AS scope, 'active' AS term, SEARCH(j, 'active', json_scope => 'JSON_VALUES') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_VALUES' AS scope, 'beta' AS term, SEARCH(j, 'beta', json_scope => 'JSON_VALUES') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_KEYS' AS scope, 'status' AS term, SEARCH(j, 'status', json_scope => 'JSON_KEYS') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_KEYS' AS scope, 'active' AS term, SEARCH(j, 'active', json_scope => 'JSON_KEYS') AS matches FROM doc
  UNION ALL
  SELECT 'JSON_KEYS' AS scope, 'beta' AS term, SEARCH(j, 'beta', json_scope => 'JSON_KEYS') AS matches FROM doc
)
ORDER BY scope, term
```

Result in BigQuery (6 rows):

| scope | term | matches |
| --- | --- | --- |
| JSON_KEYS | active | false |
| JSON_KEYS | beta | false |
| JSON_KEYS | status | true |
| JSON_VALUES | active | true |
| JSON_VALUES | beta | true |
| JSON_VALUES | status | false |

## Analyzers

`analyzer` is `LOG_ANALYZER` (default), `NO_OP_ANALYZER` or `PATTERN_ANALYZER`.

- `NO_OP_ANALYZER` doesn't split or lowercase. The whole value is one token, so the query has to equal the data exactly, including case.
- `PATTERN_ANALYZER` tokenizes with a regular expression, set in `analyzer_options` as `{"patterns": ["[0-9]+"]}`. Only text that matches the pattern becomes tokens.
- `LOG_ANALYZER` accepts `analyzer_options` with your own `delimiters` and `token_filters` (for example a normalizer with mode `NONE` to keep case).

**LOG_ANALYZER against NO_OP_ANALYZER**

```sql
SELECT analyzer, q, matches FROM (
  SELECT 'LOG_ANALYZER' AS analyzer, 'hello' AS q, SEARCH('Hello World', 'hello') AS matches
  UNION ALL
  SELECT 'NO_OP_ANALYZER', 'hello', SEARCH('Hello World', 'hello', analyzer => 'NO_OP_ANALYZER')
  UNION ALL
  SELECT 'NO_OP_ANALYZER', 'World', SEARCH('Hello World', 'World', analyzer => 'NO_OP_ANALYZER')
  UNION ALL
  SELECT 'NO_OP_ANALYZER', 'Hello World', SEARCH('Hello World', 'Hello World', analyzer => 'NO_OP_ANALYZER')
)
ORDER BY analyzer, q
```

Result in BigQuery (4 rows):

| analyzer | q | matches |
| --- | --- | --- |
| LOG_ANALYZER | hello | true |
| NO_OP_ANALYZER | Hello World | true |
| NO_OP_ANALYZER | World | false |
| NO_OP_ANALYZER | hello | false |

The pattern analyzer analyzes the query too, so a query term that matches no pattern produces no tokens and errors:

**a digits-only analyzer**

```sql
SELECT * FROM (
  SELECT 'PATTERN_ANALYZER, [0-9]+' AS analyzer, '12' AS query, SEARCH('ref=ab12cd34', '12', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}') AS matches
  UNION ALL
  SELECT 'PATTERN_ANALYZER, [0-9]+', '34', SEARCH('ref=ab12cd34', '34', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}')
  UNION ALL
  SELECT 'PATTERN_ANALYZER, [0-9]+', '1', SEARCH('ref=ab12cd34', '1', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}')
  UNION ALL
  SELECT 'LOG_ANALYZER', '12', SEARCH('ref=ab12cd34', '12')
  UNION ALL
  SELECT 'LOG_ANALYZER', 'ab12cd34', SEARCH('ref=ab12cd34', 'ab12cd34')
)
ORDER BY analyzer, query
```

Result in BigQuery (5 rows):

| analyzer | query | matches |
| --- | --- | --- |
| LOG_ANALYZER | 12 | false |
| LOG_ANALYZER | ab12cd34 | true |
| PATTERN_ANALYZER, [0-9]+ | 1 | false |
| PATTERN_ANALYZER, [0-9]+ | 12 | true |
| PATTERN_ANALYZER, [0-9]+ | 34 | true |

**the query ab under a digits-only analyzer**

```sql
SELECT SEARCH('ref=ab12cd34', 'ab', analyzer => 'PATTERN_ANALYZER', analyzer_options => '{"patterns": ["[0-9]+"]}') AS matches
```

BigQuery rejects this query:

```text
Search query 'ab' results in 0 tokens.
```

Whatever analyzer and options you use in the query must match the ones the index was built with, or the index isn't used.

## Search indexes

```sql
CREATE SEARCH INDEX logs_idx ON mydataset.logs (message);

-- or index every STRING and JSON column, now and in the future
CREATE SEARCH INDEX logs_idx ON mydataset.logs (ALL COLUMNS);
```

The index is built in the background. The query works the whole time and returns correct results, using the index only for the part that is indexed. You can see progress in `INFORMATION_SCHEMA.SEARCH_INDEXES` (`coverage_percentage`, `index_status`). An index is only built for tables of 10 GB or more. On a smaller table nothing is populated and the job reports `BASE_TABLE_TOO_SMALL`. You can't index a view, a materialized view or an external table, renaming the table invalidates the index, and a DML statement that modifies the indexed table doesn't use it.

### What we measured

We built a table of 40 million log lines (12.2 GB), each about 290 bytes of text with two 128 character hex tokens, and 10 rows with a rare token. The index covered only the `message` column and took about 25 minutes to reach 100% coverage. It was 5.9 GB, 48% of the table, because every row carries two unique long tokens. That is a bad case for index size. All runs had the cache turned off.

| Query                                                                | Index used                    | Bytes processed | Slot time |
| -------------------------------------------------------------------- | ----------------------------- | --------------- | --------- |
| `SEARCH(message, 'zqx17')`, 1 matching row                           | yes                           | 126.7 MB        | 1.0 s     |
| `STARTS_WITH(message, 'worker-17 handled request dc2de6...')`, 1 row | yes                           | 259.7 MB        | 1.5 s     |
| `SEARCH(message, 'E\\-7731')`, 10 rows                               | yes                           | 698.9 MB        | 167.8 s   |
| `SEARCH(level, 'zzz')`, `level` not indexed                          | no, `UNINDEXED_SEARCH_FIELDS` | 253.3 MB        | 5.4 s     |

The same `SEARCH` over `message` without an index has an upper bound of 11.7 GB, so the rare token was found by reading about 1% of that. Two details matter for the bill:

- The dry run doesn't know about the index. It reported 11,740,337,306 bytes for the first query after the index was ready, and `maximum_bytes_billed` is checked against that estimate. A limit of 1 MB failed with "11740905472 or higher required" even though the query reads 127 MB. See [controlling costs](https://docs.querylab.io/bigquery/controlling-costs/).
  Querylab.io's per-query byte limit is checked against that same estimate, so an indexed query can trip it; the warning's Maybe it'll fit button runs the query anyway, and BigQuery refuses it only if it really does bill over the limit ([Cost limits](https://docs.querylab.io/writing-sql/cost-limits/)).
- The cost depends on how many tokens match. The second `SEARCH` found 10 rows and read 5.5 times the bytes of the first, and spent 168 slot seconds, because the hit list for `E-7731` is much longer.

Google's search guide lists the other forms the index can speed up when you compare a column with literals: `=`, `IN`, `LIKE 'prefix%'`, `STARTS_WITH`, `ENDS_WITH`, and some `JSON_VALUE` and `UPPER`/`LOWER` forms. We confirmed `STARTS_WITH`. We didn't test `LIKE '%text%'` on the indexed table: an unindexed run would read the whole 12 GB, and Google doesn't list it. Find out from the job's `searchStatistics`:

```bash
bq show --format=prettyjson -j JOB_ID
```

`indexUsageMode` is `FULLY_USED`, `PARTIALLY_USED` or `UNUSED`, and `indexUnusedReasons` says why.

## What it costs

On-demand queries pay for bytes processed, as in the table above. Building and refreshing the index is free up to a per-organization size limit, after which you need your own reservation. Index storage is billed once the index is populated and active, and a partially built index is billed for all the storage it already has. Google says queries that use `SEARCH` or an index are not accelerated by BI Engine. To find rows by meaning, not tokens, see [vector search](https://docs.querylab.io/bigquery/vector-search/).

Run SEARCH over a line of text and see which terms match. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to search in BigQuery](https://cloud.google.com/bigquery/docs/search-intro)

- [Manage search indexes](https://cloud.google.com/bigquery/docs/search-index)

- [Search indexed data](https://cloud.google.com/bigquery/docs/search)

- [Search functions, SEARCH](https://cloud.google.com/bigquery/docs/reference/standard-sql/search_functions#search)

- [Work with text analyzers](https://cloud.google.com/bigquery/docs/text-analysis-search)