[ Web Proxy ]
URL:
Viewing: https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-ai-if [Back]  [Original]
The AI.IF function  |  BigQuery  |  Google Cloud Documentation Skip to main content
Google Cloud Documentation [Google Cloud Documentation]
Technology areas Cross-product tools
Search:
Console
[]
Start free
Overview Guides Reference Samples Resources
Google Cloud Documentation [Google Cloud Documentation]
Send feedback Stay organized with collections Save and categorize content based on your preferences.

The AI.IF function

This document describes the AI.IF function, which uses a Gemini Enterprise Agent Platform Gemini model to evaluate a condition described in natural language and returns a BOOL.

Tip: When processing large datasets, use the optimized mode (Preview) with AI.IF to reduce large language model (LLM) token costs and query latency.

You can use the AI.IF function to filter and join data based on conditions described in natural language or multimodal input. The following are common use cases:

Input

AI.IF accepts the following types of input:

When you analyze unstructured data, that data must meet the following requirements:

This function passes your input to a Gemini model and incurs charges in Gemini Enterprise Agent Platform each time it's called. For information about how to view these charges, see Track costs.

Syntax

AI.IF(
  [ prompt => ] PROMPT
  [, examples => EXAMPLES ]
  [, connection_id => 'CONNECTION' ]
  [, endpoint => 'ENDPOINT' ]
  [, embeddings => EMBEDDINGS ]
  [, optimization_mode => 'OPTIMIZATION_MODE' ]
  [, max_error_ratio => MAX_ERROR_RATIO ]
)

Arguments

AI.IF takes the following arguments.

Output

AI.IF returns a BOOL based on evaluation of the condition in the input prompt.

If the call to Gemini Enterprise Agent Platform is unsuccessful for any reason, such as exceeding quota or model unavailability, then the function returns NULL.

Examples

The following examples show how to use the AI.IF function to filter text and join multimodal data.

Filter text by topic

The following query uses the AI.IF function to filter news stories to those that cover a natural disaster:

SELECT
  title, body
FROM
  `bigquery-public-data.bbc_news.fulltext`
WHERE
  AI.IF(('The following news story is about a natural disaster: ', body),
    endpoint => 'gemini-2.5-pro');

The result is similar to the following:

+----------------------------------+---------------------------------------------+
| title                            | body                                        |
+----------------------------------+---------------------------------------------+
| Tsunami 'to hit Sri Lanka banks' | Sri Lanka's banks face hard times following |
|                                  | December's tsunami disaster...              |
| ...                              | ...                                         |
+----------------------------------+---------------------------------------------+

Filter text by topic with optimized mode

The following query uses AI.IF to find news that covers a natural disaster using optimized mode (Preview):

SELECT
  title,
  body
FROM
  `bigquery-public-data.bbc_news.fulltext`
WHERE
  AI.IF(
    ('The following news story is about a natural disaster: ', body),
    embeddings => AI.EMBED(body, endpoint => 'text-embedding-005', task_type => 'CLASSIFICATION').result,
    optimization_mode => 'MINIMIZE_COST'
  );

For this example, embeddings are generated on-the-fly. In practice, we recommend that you materialize embeddings so that they can be reused. For more information, see Optimize AI function costs.

Use a subjective condition

The following query provides examples to the AI.IF function to show what counts as an emotional review. Because "emotion" is a subjective quality, you might provide different expected results in different contexts.

SELECT
  review,
  AI.IF(
    ("The review is emotional:", review),
    endpoint => 'gemini-2.5-pro',
    examples => [
      ("I really love this product", TRUE),
      ("The product performed extremely well", FALSE)]) AS is_emotional
FROM (
    SELECT "This product did everything it was supposed to" AS review
    UNION ALL
    SELECT "This product was absolutely incredible!!" AS review);

The result is similar to the following:

+------------------------------------------------+--------------+
| review                                         | is_emotional |
+------------------------------------------------+--------------+
| This product was absolutely incredible!!       | true         |
| This product did everything it was supposed to | false        |
+------------------------------------------------+--------------+

Filter images

The following query creates an external table from images of pet products stored in a publicly available Cloud Storage bucket. Then, it filters the results to images that contain a ball.

-- Create a dataset
CREATE SCHEMA IF NOT EXISTS cymbal_pets;

-- Create an object table
CREATE OR REPLACE EXTERNAL TABLE cymbal_pets.product_images
WITH CONNECTION us.example_connection
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']
);

-- Filter images in the object table
SELECT
  OBJ.GET_READ_URL(ref).url AS signed_url,
FROM
  `cymbal_pets.product_images`
WHERE
  AI.IF(('The image contains a ball.', ref), endpoint => 'gemini-2.5-pro');

Join tables based on image content

The following queries create a table of product data and a table of product images. The tables are joined based on whether the image is of the product.

-- Create a dataset
CREATE SCHEMA IF NOT EXISTS cymbal_pets;

-- Load a non-object table
LOAD DATA OVERWRITE cymbal_pets.products
FROM
  FILES(
    format = 'avro',
    uris = [
      'gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/tables/products/products_*.avro']);

-- Create an object table
CREATE OR REPLACE EXTERNAL TABLE cymbal_pets.product_images
  WITH CONNECTION us.example_connection
  OPTIONS (
    object_metadata = 'SIMPLE',
    uris = ['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']);

-- Join the standard table and object table
SELECT product_name, brand, signed_url
FROM
  cymbal_pets.products INNER JOIN
  EXTERNAL_OBJECT_TRANSFORM(TABLE `cymbal_pets.product_images`, ['SIGNED_URL']) as images
ON
  AI.IF(
    (
      """You will be provided an image of a pet product.
      Determine if the image is of the following pet toy: """,
      products.product_name,
      images.ref
    ),
    endpoint => 'gemini-2.5-pro')
WHERE
  products.category = "Toys" AND
  products.brand = "Fluffy Buns";

Filter audio by speech topic

The following queries create a table of audio data stored in a publicly available Cloud Storage bucket. The query filters the audio samples to those that contain speech discussing a large language model.

-- Create a dataset
CREATE SCHEMA IF NOT EXISTS audio_repo;

-- Create an object table with audios
CREATE OR REPLACE EXTERNAL TABLE audio_repo.prompt_audio
WITH CONNECTION us.test_connection
OPTIONS (
  object_metadata = 'SIMPLE',
  uris = ['gs://cloud-samples-data/generative-ai/audio/*.mp3']
);

-- Filter audios in the object table
SELECT
  OBJ.GET_READ_URL(ref).url AS signed_url,
FROM
  `audio_repo.prompt_audio`
WHERE
  AI.IF(('Does the audio talk about large language models? ', ref),
    endpoint => 'gemini-2.5-pro');

Handle inference errors

The following query filters news stories but sets max_error_ratio to 0.05, meaning the query fails if more than 5% of rows return an error during inference:

SELECT
  title, body
FROM
  `bigquery-public-data.bbc_news.fulltext`
WHERE
  AI.IF(('The following news story is about a natural disaster: ', body),
    endpoint => 'gemini-2.5-pro'
    max_error_ratio => 0.05);

If the query exceeds the 0.05 error ratio, it fails and returns an error message similar to the following: Query failed because AI functions exceeded their allowed error ratio

The AI.IF and AI.GENERATE_BOOL functions both use models to generate a boolean value in response to a prompt. The following differences can help you choose which function to use:

Locations

You can run AI.IF in all of the regions that support Gemini models, and also in the US and EU multi-regions.

Quotas and limits

For quota and limit information, see Generative AI functions in the BigQuery quotas and limits reference.

What's next

Send feedback

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-08-11 UTC.

Need to tell us more? [[["Easy to understand","easyToUnderstand","thumb-up"],["Solved my problem","solvedMyProblem","thumb-up"],["Other","otherUp","thumb-up"]],[["Hard to understand","hardToUnderstand","thumb-down"],["Incorrect information or sample code","incorrectInformationOrSampleCode","thumb-down"],["Missing the information/samples I need","missingTheInformationSamplesINeed","thumb-down"],["Other","otherDown","thumb-down"]],["Last updated 2026-08-11 UTC."],[],[]]
Web Proxy Viewer  |  New URL  |  Original Page