The AI.IF function

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

This document describes theAI.IF function, which uses aVertex AI Gemini model toevaluate a condition described in natural language and returns aBOOL.

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

• Sentiment analysis: Find customer reviews with negative sentiment.
• Topic analysis: Identify news articlesrelated to a specific subject.
• Image analysis: Select images thatcontain a specific item.
• Security: Identify suspicious emails.

Input

AI.IF accepts the following types of input:

• Text data from standard tables.
• ObjectRefRuntimevalues that are generated by theOBJ.GET_ACCESS_URL function.You can useObjectRefvalues from standard tables as input to theOBJ.GET_ACCESS_URL function.(Preview)

When you analyze unstructured data, that data must meet the followingrequirements:

• Content must be in one of the supported formats that aredescribed in the Gemini API modelmimeType parameter.
• For more information about accepted multimodal input, see thetechnical specificationsfor Gemini.

This function passes your input to a Gemini model andincurs charges in Vertex AI each time it's called.For information about how to view these charges, seeTrack costs.

Syntax

AI.IF([prompt=>]'PROMPT'[,connection_id=>'CONNECTION'][,endpoint=>'ENDPOINT'])

Arguments

AI.IF takes the following arguments:

• PROMPT: aSTRING orSTRUCT value that specifies thePROMPT value to send to the model. The prompt must be the first argument that you specify. You can provide the value in the following ways:
  • Specify aSTRING value. For example, 'This is a prompt.'
  • Specify aSTRUCT value that contains one or more fields. You can use the following types of fields within theSTRUCT value:

    Field type	Description	Examples
    STRING or ARRAY<STRING>	A string literal, array of string literals, or the name of aSTRING column.	String literal: 'This is a prompt.' String column name: my_string_column
    ObjectRefRuntime or ARRAY<ObjectRefRuntime>	AnObjectRefRuntime value returned by theOBJ.GET_ACCESS_URL function. TheOBJ.GET_ACCESS_URL function takes anObjectRef value as input, which you can provide by either specifying the name of a column that containsObjectRef values, or by constructing anObjectRef value. ObjectRefRuntime values must have theaccess_url.read_url anddetails.gcs_metadata.content_type elements of the JSON value populated. Your input can contain at most one video object.	Function call withObjectRef column: OBJ.GET_ACCESS_URL(my_objectref_column, 'r') Function call with constructedObjectRef value: OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image.jpg', 'myconnection'), 'r')

    The function combinesSTRUCT fields similarly to aCONCAT operation and concatenates the fields in their specified order. The same is true for the elements of any arrays used within the struct. The following table shows some examples ofSTRUCT prompt values and how they are interpreted:

    Struct field types	Struct value	Semantic equivalent
    STRUCT<STRING, STRING, STRING>	('Describe the city of ', my_city_column, ' in 15 words')	'Describe the city ofmy_city_column_value in 15 words'
    STRUCT<STRING, ObjectRefRuntime>	('Describe the following city', OBJ.GET_ACCESS_URL(image_objectref_column, 'r'))	'Describe the following cityimage'

• CONNECTION: aSTRING value specifying the connectionto use to communicate with the model, in the format[PROJECT_ID].LOCATION.CONNECTION_ID.For example,myproject.us.myconnection.

  If you don't specify a connection, then the query uses yourend-user credentials.

  For information about configuring permissions, seeSet permissions for BigQuery ML generative AI functions that call Vertex AI models.

• ENDPOINT: aSTRING value that specifies the Vertex AIendpoint to use for the model. You can specify anygenerally availableorpreviewGemini model. If you specify the model name,BigQuery ML automatically identifies and uses the full endpointof the model. If you don't specify anENDPOINT value,BigQuery ML dynamically chooses a model based on your query tohave the best cost to quality tradeoff for the task.You can also specify theglobal endpoint.For example, to usegemini-3-pro-preview, specify the following endpoint:

  https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/gemini-3-pro-preview

  Note: Don't use the global endpoint if you have requirements for the dataprocessing location, because when you use the global endpoint, you can'tcontrol or know the region where your processing requests are handled.

Output

AI.IF returns aBOOL based on evaluation of the condition in theinput prompt.

If the call to Vertex AI is unsuccessful for any reason,such as exceeding quota or model unavailability, then the function returnsNULL.

Examples

The following examples show how to use theAI.IF function to filter textand join multimodal data.

Filter text by topic

The following query uses theAI.IF function to filter newsstories to those that cover a natural disaster:

SELECTtitle,bodyFROM`bigquery-public-data.bbc_news.fulltext`WHEREAI.IF(('The following news story is about a natural disaster: ',body));

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

SELECTtitle,bodyFROM`bigquery-public-data.bbc_news.fulltext`WHEREAI.IF(('This news is related to Google: ',body))ANDcategory='tech';-- Non-AI filters are evaluated first

-- Create a datasetCREATESCHEMAIFNOTEXISTScymbal_pets;-- Create an object tableCREATEORREPLACEEXTERNALTABLEcymbal_pets.product_imagesWITHCONNECTIONus.example_connectionOPTIONS(object_metadata='SIMPLE',uris=['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']);-- Filter images in the object tableSELECTSTRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url)ASsigned_url,FROM`cymbal_pets.product_images`WHEREAI.IF(('The image contains a ball.',OBJ.GET_ACCESS_URL(ref,'r')));

-- Create a datasetCREATESCHEMAIFNOTEXISTScymbal_pets;-- Load a non-object tableLOADDATAOVERWRITEcymbal_pets.productsFROMFILES(format='avro',uris=['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/tables/products/products_*.avro']);-- Create an object tableCREATEORREPLACEEXTERNALTABLEcymbal_pets.product_imagesWITHCONNECTIONus.example_connectionOPTIONS(object_metadata='SIMPLE',uris=['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']);-- Join the standard table and object tableSELECTproduct_name,brand,signed_urlFROMcymbal_pets.productsINNERJOINEXTERNAL_OBJECT_TRANSFORM(TABLE`cymbal_pets.product_images`,['SIGNED_URL'])asimagesONAI.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-flash')WHEREproducts.category="Toys"ANDproducts.brand="Fluffy Buns";

-- Create a datasetCREATESCHEMAIFNOTEXISTSaudio_repo;-- Create an object table with audiosCREATEORREPLACEEXTERNALTABLEaudio_repo.prompt_audioWITHCONNECTIONus.test_connectionOPTIONS(object_metadata='SIMPLE',uris=['gs://cloud-samples-data/generative-ai/audio/*.mp3']);-- Filter audios in the object tableSELECTSTRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url)ASsigned_url,FROM`audio_repo.prompt_audio`WHEREAI.IF(('Does the audio talk about large language models? ',OBJ.GET_ACCESS_URL(ref,'r')));

TheAI.IF andAI.GENERATE_BOOLfunctions both use models to generate aboolean value in response to a prompt. The following differences can help youchoose which function to use:

• Prompt Optimization:AI.IF automaticallystructuresyour prompts to improve the quality of the output.
• Input:AI.GENERATE_BOOL lets you specify model parameters to use.
• Output:AI.IF returns aBOOL value, which makes it easier towork with in queries.AI.GENERATE_BOOL returns aSTRUCT that containsaBOOL value, as well as additional information about the model call, whichis useful if you need to view details such as thesafety ratingor API response status.
• Error handling: IfAI.IF produces an error for any input, then thefunction returnsNULL.AI.GENERATE_BOOL records details about theerrors in its output.

Locations

You can runAI.IF in all of theregionsthat support Gemini models, and also in theUS andEUmulti-regions.

Quotas

SeeGenerative AI functions quotas and limits.

What's next

• For more information about using Vertex AI models togenerate text and embeddings, seeGenerative AI overview.
• For more information about using Cloud AI APIs to perform AI tasks, seeAI application overview.
• For more information about supported SQL statements and functions forgenerative AI models, seeEnd-to-end user journeys for generative AI models.
• To use this function in a tutorial, seePerform semantic analysis with managed AI functions.