The AI.AGG function

The AI.AGG function uses a Vertex AI Gemini model to aggregate data based on natural language instructions that you provide. The function can process text or multimodal data and returns a single string.

Use cases

The following are common use cases for the AI.AGG function:

  • Sentiment analysis of user reviews. What is the overall sentiment of user feedback? What are the common positive and negative aspects across reviews? How do users feel about specific aspects of the product (size, price, value)?
  • Content summarization of multimodal data. What kinds of animals are captured in these images? What are the most common shoe brands in the user-uploaded pictures?
  • Analysis of AI agent performance. What kinds of tasks did the agent complete the fastest? What kinds of tasks required the most user interactions before completion?
  • Log analysis. What parts of the application did users interact with the most? What were the most common final user actions before disconnecting?
  • Error clustering and deduplication: Which internal errors or test failures share the same root cause?

We recommend the AI.AGG function for aggregation tasks instead of the AI.GENERATE and AI.GENERATE_TEXT general-purpose AI functions for the following reasons:

  • The AI.AGG function automatically performs multi-level aggregation through batching data and aggregating the batch results. Because of this process, the AI.AGG function is capable of analyzing data that exceeds the size of the standard Gemini context window. The general-purpose AI functions require manually writing multi-level batching logic to achieve similar results. For queries that are small enough to not require multi-level aggregation for high-quality results, the AI.AGG function will automatically optimize to skip this process.
  • The AI.AGG function returns one result per input group, while the AI.GENERATE function returns one result per input row and the AI.GENERATE_TEXT function returns a table with additional information.

Syntax

AI.AGG(
  [ DISTINCT ]
  INPUT,
  INSTRUCTION
  [, connection_id => 'CONNECTION']
  [, endpoint => 'ENDPOINT']
  [, max_error_ratio => MAX_ERROR_RATIO]
  [, output_schema => 'OUTPUT_SCHEMA']
)

Arguments

The AI.AGG function takes the following arguments:

  • INPUT: the data to be aggregated, as either a STRING value or a STRUCT object consisting of STRING values, ObjectRefRuntime values, and arrays of STRING and ObjectRefRuntime values. ObjectRefRuntime values reference text or multimodal data in Cloud Storage. Text and multimodal data must meet the technical specifications for the version of Gemini that you plan to use. The AI.AGG function supports image, video, audio, text, or PDF files represented by ObjectRefRuntime values. Rows can contain at most 10 images, or 1 audio, video, or PDF file.

    For the best performance, we recommend no more than 1,000 distinct groups and the following row counts:

    • For queries on rows containing only text and single images, an input of no more than 20 million rows per query.
    • For queries on rows containing PDF documents, no more than 100,000 rows per query.
    • For queries on rows containing video files, no more than 1,000 rows per query.
    • For queries on rows containing audio files, no more than 10,000 rows per query.

    Queries that exceed these limits might fail and return no results, or only partial results. See the max_error_ratio argument for details on how the AI.AGG function handles partial results.

    Inside a VPC Service Controls perimeter, the AI.AGG function can't process ObjectRef values that use delegated access, such as the ref column of an object table. Delegated access generates a signed HTTPS URL for the object, and Agent Platform blocks HTTP and HTTPS fetches for projects inside a perimeter, so the function returns the error INVALID_ARGUMENT: HTTP links are not supported for requests restricted by VPCSC. in the status field of its output. To analyze the object, pass a single-argument OBJ.MAKE_REF(uri) value instead, which uses direct access and sends the Cloud Storage URI to the model without generating a signed URL.

  • INSTRUCTION: the natural language aggregation prompt to apply to the input data, as either a string or query parameter.

  • CONNECTION: a Cloud resource connection in the [PROJECT_ID.]LOCATION.CONNECTION_ID format. This connection is used to communicate with Vertex AI and Cloud Storage. If you don't specify a connection, then your end-user credentials are used.

  • ENDPOINT: the Vertex AI endpoint to use, which can consist of any Gemini model that doesn't require thinking budget. If you only include the model ID, BigQuery automatically identifies and uses the full endpoint in the region where the query is processed. If you don't specify any endpoint information, BigQuery chooses a model for you.

    BigQuery supports the following models:

    • gemini-3.1-flash-lite
    • gemini-3.5-flash
    • gemini-3.5-flash-lite
    • gemini-3.6-flash
    • gemini-3.7-flash
    • gemini-3.8-flash
    Agent Platform only supports multi-regional endpoints for these models. Regional endpoints aren't supported. If you specify a short endpoint name that omits the region, such as gemini-3.5-flash, then BigQuery selects an endpoint according to the following rules:
    • If your query is run in the us region, or any single region in the US, then BigQuery uses the us endpoint.
    • If your query is run in the eu region, or any single region in the EU other than europe-west2 or europe-west6, then BigQuery uses the eu endpoint.
    • For all other locations, including europe-west2 and europe-west6, BigQuery uses the global endpoint.
    To specify a specific endpoint, use a fully qualified multi-regional endpoint name in one of the following formats:
    • https://aiplatform.us.rep.googleapis.com/v1/projects/PROJECT_ID/locations/us/publishers/google/models/MODEL_ID
    • https://aiplatform.eu.rep.googleapis.com/v1/projects/PROJECT_ID/locations/eu/publishers/google/models/MODEL_ID
    • https://aiplatform.googleapis.com/v1/projects/PROJECT_ID/locations/global/publishers/google/models/MODEL_ID
    If your query runs in the asia-south1 region, then you must use the fully qualified global endpoint name.

  • MAX_ERROR_RATIO: a FLOAT64 value between 0.0 and 1.0 that contains the maximum acceptable ratio of row-level inference failures to rows processed on this function. If this value is exceeded, then the query fails and BigQuery returns an error message that describes the most frequent types of errors. For example, if the value is 0.3, then the query fails if more than 30% of rows processed fail to return results. If max_error_ratio is set for multiple functions, the query fails if the ratio is exceeded on any function. The default value is 1.0. However, the query still fails if inference fails for every row.

  • OUTPUT_SCHEMA: a STRING value that specifies the schema of the output as a comma-separated list of fields. Each field consists of a name, a data type, and an optional OPTIONS clause in which you can specify a description of the field. You can also specify NOT NULL for a field. We recommend using NOT NULL for most fields, otherwise the LLM might return NULL for some fields in the response. The following example shows how to specify an output schema that contains two string fields, name and state, with a description for the state field and the name field required to be NOT NULL:

    OUTPUT_SCHEMA => '''
name STRING NOT NULL,
state STRING OPTIONS(description = 'The 2-letter abbreviation of the state name')
'''

    Supported data types include STRING, INT64, FLOAT64, BOOL, ARRAY, and STRUCT. For a STRUCT data type, you can specify the OPTIONS clause on any of its subfields:

    OUTPUT_SCHEMA => '''
location STRUCT<city STRING,
                state STRING OPTIONS(description = 'The 2-letter abbreviation of the state name')>
'''

Output

If you do not specify the OUTPUT_SCHEMA argument, the AI.AGG function returns a STRING value. If you do specify it, the AI.AGG function returns a STRUCT value that conforms to the specified schema. If you use the AI.AGG function with a GROUP_BY statement, then the function returns one value for each input group.

If a call to Vertex AI is unsuccessful for any reason, such as exceeding a quota or model unavailability, then the function returns partial results. If all calls to Vertex AI fail, the number of invalid rows or rows that are not successfully processed exceeds max_error_ratio, or if all the input rows are invalid, then the function returns an error.

Function output is capped at 10,000 tokens per group.

Examples

These examples use the AI.AGG function for various sentiment analysis, content summarization, and data exploration use cases.

User sentiment analysis

The following example uses the AI.AGG function to analyze viewer sentiment for a set of movies:

SELECT
  title,
  movie_id,
  AI.AGG(
    review,
    'You will be given user-provided reviews of a movie. Summarize the overall sentiment towards the movie.'
  ) AS sentiment
FROM `bigquery-public-data.imdb.reviews`
WHERE movie_id IN ('tt0339384', 'tt0084787', 'tt0029850')
GROUP BY movie_id, title;

The result is similar to the following:

+---------------+-----------+--------------------------------------------------+
|     title     | movie_id  |           sentiment                              |
+---------------+-----------+--------------------------------------------------+
| Mickey's      | tt0339384 | The movie is overwhelmingly well-received, with  |
| PhilharMagic  |           | reviewers consistently praising its spectacular  |
|               |           | 3D effects as the best they've seen. Many        |
|               |           | found it incredibly joyful and exciting,         |
|               |           | bringing smiles to their faces and leaving them  |
|               |           | breathless. The musical numbers and beloved      |
|               |           | Disney characters also significantly contributed |
|               |           | to this positive experience, with some viewers   |
|               |           | even watching the show multiple times.           |
+---------------+-----------+--------------------------------------------------+
|   Alexander   | tt0029850 | Overall, the sentiment towards                   |
|   Nevsky      |           | "Alexander Nevsky" is overwhelmingly positive,   |
|               |           | with many reviewers calling it a cinematic       |
|               |           | masterpiece and a work of art. People especially |
|               |           | love the stunning visuals, particularly the      |
|               |           | iconic battle on the ice, and Prokofiev's        |
|               |           | magnificent musical score. The film is also      |
|               |           | highly regarded for its historical and artistic  |
|               |           | significance, with many noting how it transcends |
|               |           | its propagandistic elements to become great art. |
|               |           | While some acknowledge the film's age and        |
|               |           | its overt propaganda, these points are generally |
|               |           | outweighed by its artistic achievements and      |
|               |           | lasting impact.                                  |
+---------------+-----------+--------------------------------------------------+
|   The Thing   | tt0084787 | The overall sentiment towards "The Thing" is     |
|               |           | overwhelmingly positive, with many reviewers     |
|               |           | hailing it as a masterpiece and one of the best  |
|               |           | horror/sci-fi films ever made. People            |
|               |           | consistently praise its intense tension and      |
|               |           | paranoia, the groundbreaking and enduring        |
|               |           | quality of its practical special effects, and    |
|               |           | its intelligent, suspenseful story. John         |
|               |           | Carpenter's direction and the strong             |
|               |           | ensemble acting, particularly Kurt               |
|               |           | Russell's performance, are also frequently       |
|               |           | highlighted. Reviewers often note that the film  |
|               |           | has aged incredibly well, remaining fresh and    |
|               |           | impactful decades later. While there were some   |
|               |           | initial criticisms regarding its box office      |
|               |           | performance and a few minor points about the     |
|               |           | score or character depth, these are largely      |
|               |           | overshadowed by the widespread acclaim for its   |
|               |           | psychological depth, terrifying effects, and     |
|               |           | lasting suspense.                                |
+---------------+-----------+--------------------------------------------------+

Common categories

The following example uses the AI.AGG function to determine the most-used languages in a set of comments:

WITH sample_data AS (
  SELECT title, comment
  FROM `bigquery-public-data.samples.wikipedia`
  WHERE comment IS NOT NULL
  LIMIT 5000
  )
SELECT
  AI.AGG(
    TO_JSON_STRING(t),
    'These are wikipedia comments. What are the most used languages?'
  ) AS most_used_language
FROM sample_data as t;

The result is similar to the following:

+-----------------------------------------------------------------------------+
|                          most_used_language                                 |
+-----------------------------------------------------------------------------+
| The most used language in the Wikipedia comments is English, by a           |
| significant margin. Other languages that appear frequently include French,  |
| German, Italian, and Dutch.                                                 |
+-----------------------------------------------------------------------------+

Image content summarization

The following example uses the AI.AGG function to summarize the contents of a set of images:

# 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']
);

# Summarize content of images in the object table
SELECT
  AI.AGG(
    STRUCT(OBJ.GET_ACCESS_URL(ref, 'r')),
    'What are the major categories of these images?'
  ) AS category_description
FROM
  `cymbal_pets.product_images`;

The result is similar to the following:

+-----------------------------------------------------------------------------+
|                         category_description                                |
+-----------------------------------------------------------------------------+
| The images primarily fall into the category of Pet Supplies. Within this    |
| main category, the most common types are Pet Food, Pet Accessories,         |
| Aquarium Supplies, Pet Care Products (non-food), Pet Toys, and Pet          |
| Habitats. There are also a few images in an "Other" category and one image  |
| of Craft Supplies.                                                          |
+-----------------------------------------------------------------------------+

Most frequent items

The following example uses the AI.AGG function to find the most referenced companies in a set of news articles:

SELECT
  AI.AGG(
    TO_JSON_STRING(t),
    'In the following tech news articles, what are the top 3 most referenced companies, and what are they famous for?'
  ) AS tech_news_companies
FROM (SELECT * FROM `bigquery-public-data.bbc_news.fulltext` LIMIT 30000) AS t
WHERE t.category = 'tech';

The result is similar to the following:

+-----------------------------------------------------------------------------+
|                          tech_news_companies                                |
+-----------------------------------------------------------------------------+
| Here are the top 3 most referenced companies across all the provided tech   |
| news, along with what they are famous for:                                  |
|                                                                             |
| 1.  **Microsoft** (referenced 64 times)                                     |
|     **Famous for:** Its dominant **Windows operating system**, the          |
|     **Xbox gaming console** , and a wide array of **software products**     |
|     (including Office, Internet Explorer, and security tools). Microsoft is |
|     consistently mentioned for its efforts in computer security, combating  |
|     piracy, and its significant presence in both consumer electronics       |
|     and enterprise solutions.                                               |
|                                                                             |
| 2.  **Apple** (referenced 42 times)                                         |
|     **Famous for:** Its innovative and design-led                           |
|     **consumer electronics**, particularly the highly successful            |
|     **iPod digital music player** and **Macintosh computers** (including    |
|     the Mac Mini and Powerbook). Apple is also known for its iTunes online  |
|     store, its secrecy regarding new products, and its legal battles to     |
|     protect intellectual property.                                          |
|                                                                             |
| 3.  **Sony** (referenced 30 times)                                          |
|     **Famous for:** Its prominent role in **consumer electronics**          |
|     and the **gaming industry**, especially with its                        |
|     **PlayStation series of consoles** (PS2, PS3, PSP). Sony is also        |
|     recognized for its involvement in DVD technology (Blu-ray Disc),        |
|     humanoid robots (Qrio), and its broader range of consumer electronics   |
|     products.                                                               |
+-----------------------------------------------------------------------------+

Text summarization

The following example uses the AI.AGG function to summarize blog topics that are grouped by author:

WITH sample_data AS (
  SELECT *
  FROM `bigquery-public-data.hacker_news.full` AS t
  WHERE t.text IS NOT NULL
    AND t.type = 'story'
    AND t.by IN UNNEST(['amichail', 'kids_book', 'whoishiring', 'michaelmayer'])
  LIMIT 30000
  )
SELECT
  t.by,
  AI.AGG(
    t.text,
    'These are the hacker news blog posts of a single user. Tell me what topics the user talks about the most. Keep it concise.'
    ) AS common_topic
FROM sample_data AS t
GROUP BY t.by;

The result is similar to the following:

+--------------+---------------------------------------------------------------+
|     by       |                          common_topic                         |
+--------------+---------------------------------------------------------------+
|   amichail   | The user talks most about Artificial Intelligence and         |
|              | Machine Learning, followed by general Technology and          |
|              | Software Development (especially the Apple ecosystem), and    |
|              | Gaming and Game Development. They also frequently discuss     |
|              | social dynamics, human behavior, and education.               |
+--------------+---------------------------------------------------------------+
| whoishiring  | This user primarily discusses job-related topics on Hacker    |
|              | News. They frequently provide guidelines for job postings     |
|              | (for companies), job seekers ("Who wants to be hired?"), and  |
|              | freelancers. Remote work is a significant sub-theme, and      |
|              | they show a specific interest in connecting refugees with     |
|              | employment opportunities. They also touch on general Hacker   |
|              | News community guidelines and job search resources.           |
+--------------+---------------------------------------------------------------+
| michaelmayer | The user primarily discusses mobile technology (especially    |
|              | Apple and Android), web design and development, social media  |
|              | (particularly Twitter and Facebook), giveaways, bundles, and  |
|              | inspiration and creativity.                                   |
+--------------+---------------------------------------------------------------+
|  kids_book   | The user most frequently discusses anti-bullying, often       |
|              | promoting the children's book "Butterfly Me" as a resource.   |
|              | They also frequently talk about children's books in general,  |
|              | particularly those by Evon Latrail, including "When Mommy     |
|              | Went to Heaven" which addresses death and grief for children. |
|              | Giveaways and promotions, especially for "Butterfly Me,"      |
|              | are also a recurring theme.                                   |
+--------------+---------------------------------------------------------------+

Handle inference errors

The following query summarizes a particular author's blog posts, but sets max_error_ratio to 0.05, meaning the query fails if more than 5% of rows encounter an error during inference or input validation:

WITH sample_data AS (
  SELECT *
  FROM `bigquery-public-data.hacker_news.full` AS t
  WHERE t.text IS NOT NULL
    AND t.type = 'story'
    AND t.by = 'kids_book'
  LIMIT 30000
  )
SELECT
  t.by,
  AI.AGG(
    t.text,
    'These are the hacker news blog posts of a single user. Tell me what topics the user talks about the most. Keep it concise.',
    max_error_ratio => 0.05
    ) AS common_topic
FROM sample_data AS t

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.

Using output_schema for structured output

The following example uses a similar query to the previous examples, but with a structured output schema:

WITH sample_data AS (
  SELECT *
  FROM `bigquery-public-data.hacker_news.full` AS t
  WHERE t.text IS NOT NULL
    AND t.type = 'story'
    AND t.by IN UNNEST(['amichail', 'kids_book', 'whoishiring', 'michaelmayer'])
  LIMIT 30000
  )
SELECT
  t.by,
  AI.AGG(
    t.text,
    'These are the hacker news blog posts of a single user. Tell me what topics the user talks about the most. Keep it concise.',
    output_schema =>
      'topics ARRAY<STRUCT<name STRING NOT NULL, explanation STRING NOT NULL>>'
    ) AS common_topic
FROM sample_data AS t
GROUP BY t.by;

The result is similar to the following:

+--------------+------------------------------------------------+------------------------------+
|      by      |        common_topic.topics.explanation         |   common_topic.topics.name   |
+--------------+------------------------------------------------+------------------------------+
| michaelmayer | The user frequently discusses the iPhone,      | Mobile Technology            |
|              | iPad, and Android ecosystems, focusing on app  |                              |
|              | reviews and market trends.                     |                              |
|              +------------------------------------------------+------------------------------+
|              | A strong emphasis on visual arts, including    | Design and Creative          |
|              | typography, photography, illustration, and web | Inspiration                  |
|              | design trends.                                 |                              |
|              +------------------------------------------------+------------------------------+
|              | Technical content centered on WordPress        | Web Development and          |
|              | themes, plugins, and foundational web          | WordPress                    |
|              | standards like HTML5 and CSS3.                 |                              |
|              +------------------------------------------------+------------------------------+
|              | Occasional advice regarding freelancing,       | Professional Development and |
|              | client management, and the role of social      | Digital Marketing            |
|              | media in branding.                             |                              |
+--------------+------------------------------------------------+------------------------------+
|   amichail   | The user focuses on the indie hacker           | Software and Indie           |
|              | lifestyle, app development, and the need for   | Development                  |
|              | simplified, creative coding tools.             |                              |
|              +------------------------------------------------+------------------------------+
|              | A strong interest in how AI can automate       | AI and Future Technology     |
|              | education, augment human creativity, and       |                              |
|              | manage complex social systems.                 |                              |
|              +------------------------------------------------+------------------------------+
|              | The user advocates for self-directed, project- | Educational Reform           |
|              | based learning while critiquing traditional    |                              |
|              | computer science and mathematics education.    |                              |
|              +------------------------------------------------+------------------------------+
|              | Exploration of designing systems and           | Social and Behavioral        |
|              | incentives to nudge human behavior and improve | Engineering                  |
|              | social efficiency.                             |                              |
|              +------------------------------------------------+------------------------------+
|              | Frequent discussion of algorithmic twists on   | Game Design                  |
|              | classic puzzles and mechanics for indie game   |                              |
|              | development.                                   |                              |
+--------------+------------------------------------------------+------------------------------+
| whoishiring  | The user primarily focuses on administering    | Job Market Thread Management |
|              | and standardizing recurring Hacker News        |                              |
|              | threads like 'Who is Hiring,' 'Who wants to be |                              |
|              | hired,' and 'Freelancer' by enforcing strict   |                              |
|              | posting guidelines.                            |                              |
|              +------------------------------------------------+------------------------------+
|              | The user frequently shares and promotes        | Search Tool Promotion        |
|              | community-built tools designed to help users   |                              |
|              | navigate and filter job listings within the    |                              |
|              | platform's threads.                            |                              |
|              +------------------------------------------------+------------------------------+
|              | The user engages in administrative moderation  | Advocacy and Moderation      |
|              | tasks and occasionally advocates for specific  |                              |
|              | initiatives, such as refugee employment        |                              |
|              | programs.                                      |                              |
+--------------+------------------------------------------------+------------------------------+
|  kids_book   | The user primarily uses their platform to      | Book Promotion               |
|              | market their books, specifically 'Butterfly    |                              |
|              | Me' and 'When Mommy Went to Heaven'.           |                              |
|              +------------------------------------------------+------------------------------+
|              | A significant portion of content is dedicated  | Personal Branding and Social |
|              | to aggressive outreach and brand building      | Media                        |
|              | across platforms like Facebook, Twitter, and   |                              |
|              | Instagram.                                     |                              |
|              +------------------------------------------------+------------------------------+
|              | The user frequently utilizes Visa gift card    | Incentive Marketing          |
|              | giveaways to drive engagement and book sales.  |                              |
+--------------+------------------------------------------------+------------------------------+

Locations

You can run the AI.AGG function in any BigQuery region as long as your endpoint location supports the Gemini model that you're using. If your query processing region doesn't have a supported Gemini endpoint, you must specify a full endpoint that is supported, such as the model's global endpoint. For more information, see Google model endpoint locations.

Manage inference costs

Inference using the Vertex AI Gemini model can be a relatively expensive operation. Due to the nature of query processing in BigQuery, the actual number of rows processed by the model might differ from what you expect, particularly when running complex queries, such as JOIN or ORDER BY ... LIMIT clauses. To strictly control the number of rows processed by your complex queries, we recommended that you write the results of your query to a separate table beforehand, and then perform the Gemini inference directly on that materialized table. For information about how to view inference charges that you incur in Agent Platform, see Track costs. To estimate the token count of text input, use the AI.COUNT_TOKENS function.

Because the AI.AGG function uses batching and aggregation to analyze data that exceeds the size of the standard Gemini context window, the total number of tokens that are sent to Gemini is greater than the number of tokens in the source data.

Quotas and limits

For quota and limit information, see Generative AI functions in the BigQuery quotas and limits reference. For information about managing the cost of this function, see Control costs with token quotas.

Known issues

The AI.AGG function has the following known issues:

  • If you provide a NULL instruction to the AI.AGG function, it might return an internal error instead of an invalid query error.

What's next