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')

Arguments

AI.IF takes the following arguments:

• PROMPT: aSTRING orSTRUCT value that specifiesthe prompt to send tothe model. The prompt must be the first argument that you specify.You can provide the prompt value in the following ways:

  • Specify aSTRING value. For example,('Is Seattle a US city?').

  • Specify aSTRUCT value that contains one or more fields. You can usethe following types of fields within theSTRUCT value:

    Field type	Description	Examples
    STRING	A string literal, or the name of aSTRING column.	String literal: 'Is Seattle a US city?' String column name: my_string_column
    ARRAY<STRING>	You can only use string literals in the array.	Array of string literals: ['Is ', 'Seattle', ' a US city?']
    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')
    ARRAY<ObjectRefRuntime>	ObjectRefRuntime values returned from multiple calls to 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 calls withObjectRef columns: [OBJ.GET_ACCESS_URL(my_objectref_column1, 'r'), OBJ.GET_ACCESS_URL(my_objectref_column2, 'r')] Function calls with constructedObjectRef values: [OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image1.jpg', 'myconnection'), 'r'), OBJ.GET_ACCESS_URL(OBJ.MAKE_REF('gs://image2.jpg', 'myconnection'), 'r')]

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

    Struct field types	Struct value	Semantic equivalent
    STRUCT<STRING>	('Is Seattle a US city?')	'Is Seattle a US city?'
    STRUCT<STRING, STRING, STRING>	('Is the city of ', my_city_column, ' in India?')	'Is the city ofmy_city_column_value in India?'
    STRUCT<STRING, ARRAY<STRING>>	('Is the city of ', ['Oslo', ' in India?'])	'Is the city of Oslo in India?'
    STRUCT<STRING, ObjectRefRuntime>	('Is the city in the following image in Canada?', OBJ.GET_ACCESS_URL(image_objectref_column, 'r'))	'Is the city in the following image in Canada?'image
    STRUCT<STRING, ObjectRefRuntime, ObjectRefRuntime>	('Is the city in the first image within the country of the second image?', OBJ.GET_ACCESS_URL(city_image_objectref_column, 'r'), OBJ.GET_ACCESS_URL(country_image_objectref_column, 'r'))	'Is the city in the first image within the country of the second image?'city_imagecountry_image

• CONNECTION: aSTRING value specifying theCloud resource connectionto use. The following forms are accepted:

  • Connection name:[PROJECT_ID].LOCATION.CONNECTION_ID

    For example,myproject.us.myconnection.

  • Fully qualified connection ID:projects/PROJECT_ID/locations/LOCATION/connections/CONNECTION_ID

    For example,projects/myproject/locations/us/connections/myconnection.

  Replace the following:

  • PROJECT_ID: the project ID of the project that contains the connection.
  • LOCATION: thelocation used by the connection.
  • CONNECTION_ID: the connection ID—for example,myconnection.

    You can get this value byviewing the connection details in the Google Cloud console and copying the value in the last section of the fully qualified connection ID that is shown inConnection ID. For example,projects/myproject/locations/connection_location/connections/myconnection.

  Important

  You need to grant theVertex AI User role to the connection's service account. For more information, seeGrant or revoke a single IAM role.

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),connection_id=>'us.example_connection');

Combine filters

BigQuery optimizes queries to reduce the number of calls toGemini. The following query first filters by theequality operator, and then filters using theAI.IF function:

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

Filter images

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

-- 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')),connection_id=>'us.example_connection');

Join tables based on image content

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

-- 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),connection_id=>'us.example_connection')WHEREproducts.category="Toys"ANDproducts.brand="Fluffy Buns";

Filter audio by speech topic

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

-- 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')),connection_id=>'us.test_connection');

Related functions

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.IF automatically selects a Gemini model.AI.GENERATE_BOOL lets you specify a specific model endpoint and modelparameters 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.