The AI.GENERATE_BOOL 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.GENERATE_BOOL function, which lets youanalyze any combination of text and unstructured data. For each rowin the table, the function generates aSTRUCT that contains aBOOL value.

The function works by sending requests to a Vertex AI Geminimodel, and then returning that model's response.

You can use theAI.GENERATE_BOOL function to perform tasks such asclassification and sentiment analysis.

Prompt design can strongly affect the responses returned by themodel. For more information, seeIntroduction to prompting.

Input

Using theAI.GENERATE_BOOL function, you can use the following typesof 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.
• If you are analyzing a video, the maximum supported length is two minutes.If the video is longer than two minutes,AI.GENERATE_BOOL only returnsresults based on the first two minutes.

Syntax

AI.GENERATE_BOOL([prompt=>]'PROMPT',[,endpoint=>'ENDPOINT'][,model_params=>MODEL_PARAMS][,connection_id=>'CONNECTION'][,request_type=>'REQUEST_TYPE'])

Arguments

AI.GENERATE_BOOL takes the following arguments:

• PROMPT: aSTRING orSTRUCT value that specifiesthePROMPT value 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, or arrays containing these types,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
    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 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, STRING, STRING>	('Is the city of ', my_city_column, ' in the US?')	'Is the city ofmy_city_column_value in the US?'
    STRUCT<STRING, ObjectRefRuntime>	('Is the city in the following image in the US?', OBJ.GET_ACCESS_URL(image_objectref_column, 'r'))	'Is the city in the following image in the US?'image

• 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 selects a recent stable version ofGemini to use.

  Note: Using Gemini 2.5 models incurs charges for thethinking process.You can set a budget for the thinking process forGemini 2.5 Flash and Gemini 2.5 Flash-Lite models by usingtheMODEL_PARAMS argument to set thethinking_budget parameter.For an example, seeSet the thinking budget for a Gemini 2.5 Flash model.You can't set a budget for Gemini 2.5 Pro models.

• MODEL_PARAMS: aJSON literal that provides additional parameters tothe model. TheMODEL_PARAMS value must conform to thegenerateContent request body format.You can provide a value for any field in the request body except for thecontents field; thecontents field is populated with thePROMPTargument value.

• 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.

• REQUEST_TYPE: aSTRING value that specifies the type of inferencerequest to send to the Gemini model. The request typedetermines what quota the request uses. Valid values are asfollows:

  • SHARED: The function only usesdynamic shared quota (DSQ).
  • DEDICATED: The function only usesProvisioned Throughput quota. The function returns an invalidquery error if Provisioned Throughput quota isn't available. For more information,seeUse Vertex AI Provisioned Throughput.

  • UNSPECIFIED: The function uses quota as follows:

    • If you haven't purchased Provisioned Throughput quota,the function uses DSQ quota.
    • If you have purchased Provisioned Throughput quota,the function uses the Provisioned Throughputquota first. If requests exceed the Provisioned Throughputquota, the overflow traffic uses DSQ quota.

  The default value isUNSPECIFIED.

Output

AI.GENERATE_BOOL returns aSTRUCT value for each row in the table. The structcontains the following fields:

• result: aBOOL value containing the model's response to the prompt. Theresult isNULL if the request fails or is filtered byresponsible AI.
• full_response: a JSON value containing theresponsefrom theprojects.locations.endpoints.generateContentcall to the model. The generated text is in thetext element.
• status: aSTRING value that contains the API responsestatus for the corresponding row. This value is empty if the operation wassuccessful.

Examples

The following examples assume that you have granted theVertex AI User role to your personal account.For more information, seeRun generative AI queries with end-user credentials.

Use string input

To determine whether each city is located in the US, call theAI.GENERATE_BOOL function and select theresult field in the outputby running the following query:

SELECTcity,AI.GENERATE_BOOL(('Is ',city,' a US city?')).resultFROMUNNEST(["Seattle","Beijing","Paris","London"])city;

The result is similar to the following:

+---------+--------+| city    | result |+---------+--------+| Seattle | true   || Beijing | false  || Paris   | false  || London  | false  |+---------+--------+

Process images in a Cloud Storage bucket

The following query creates an external table from images of pet productsstored in a publicly available Cloud Storage bucket:

CREATESCHEMAIFNOTEXISTSbqml_tutorial;CREATEORREPLACEEXTERNALTABLEbqml_tutorial.product_imagesWITHCONNECTIONDEFAULTOPTIONS(object_metadata='SIMPLE',uris=['gs://cloud-samples-data/bigquery/tutorials/cymbal-pets/images/*.png']);

To determine which animals are mammals, call theAI.GENERATE_BOOL functionand select theresult field in the output by running the following query:

SELECTuri,STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url)ASsigned_url,AI.GENERATE_BOOL(("Is this cat food?",OBJ.GET_ACCESS_URL(ref,'r'))).resultFROMbqml_tutorial.product_imagesWHEREuriLIKE'%cat%';

The result is similar to the following:

Set the thinking budget for a Gemini 2.5 Flash model

The following query shows how to set themodel_params argument to set themodel's thinking budget to0 for the request:

SELECTcity,AI.GENERATE_BOOL(('Is ',city,' a US city?'),endpoint=>'gemini-2.5-flash',model_params=>JSON'{"generation_config":{"thinking_config": {"thinking_budget": 0}}}')FROMmydataset.cities;

Best Practices

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.To minimize Vertex AI charges when you useAI.GENERATE_BOOLon a subset of data using theLIMIT clause, materialize the selected data to atable first. For example, the first of the following examples is preferable tothe second one:

CREATETABLEmydataset.citiesAS(SELECTcity_namefrommydataset.customersLIMIT10.);SELECTcity,AI.GENERATE_BOOL(('Is ',city,' a US city?')).resultFROMmydataset.cities;

SELECTcity,AI.GENERATE_BOOL(('Is ',city,' a US city?')).resultFROM(SELECTcity_namefrommydataset.customersLIMIT10);

Writing the query results to a table beforehand helps you to ensure that youare sending as few rows as possible to the model.

Use Vertex AI Provisioned Throughput

You can useVertex AI Provisioned Throughputwith theAI.GENERATE_BOOL function to provide consistent high throughput forrequests. The remote model that you reference in theAI.GENERATE_BOOL functionmust use asupported Gemini modelin order for you to use Provisioned Throughput.

To use Provisioned Throughput,calculate your Provisioned Throughput requirementsand thenpurchase Provisioned Throughputquota before running theAI.GENERATE_BOOL function. When you purchaseProvisioned Throughput, do the following:

• ForModel, select the same Gemini model as the one usedby the remote model that you reference in theAI.GENERATE_BOOL function.

• ForRegion, select the same region as the dataset that containsthe remote model that you reference in theAI.GENERATE_BOOL function, withthe following exceptions:

  • If the dataset is in theUS multi-region, select theus-central1region.
  • If the dataset is in theEU multi-region, select theeurope-west4region.

After you submit the order, wait for the order to be approved and appear on theOrders page.

After you have purchased Provisioned Throughput quota, use theREQUEST_TYPE argument to determine how theAI.GENERATE_BOOL function usesthe quota.

Locations

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

Quotas

SeeVertex AI and Cloud AI service 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.