Analyzing thousands or millions of customer reviews, support tickets, application logs, and documents can be difficult with traditional SQL alone. SQL is excellent at counting records, calculating averages, and grouping structured data, but identifying recurring themes across unstructured text often requires additional processing.
BigQuery addresses this challenge with AI.AGG, a SQL function that uses a Gemini model to aggregate unstructured data according to natural-language instructions.
On October 13, 2026, Google announced that AI.AGG is generally available. The function allows developers and data analysts to summarize and analyze large collections of text and supported image data directly within BigQuery queries. It also performs multi-level aggregation automatically, helping it handle datasets that exceed a model's standard context window. <Cite refs={["turn105387search1","turn105387search0"]}/>
This article explains how AI.AGG works, how to use it in GoogleSQL, and what developers should consider when applying AI-powered aggregation to production analytics.
What Is BigQuery AI.AGG?
AI.AGG is a managed generative AI function in BigQuery. It accepts data and a natural-language instruction, processes the input using a Gemini model, and returns a single text result for each aggregation group.
Traditional SQL aggregation focuses on operations such as COUNT, SUM, MIN, and MAX. These functions work well when the required result can be calculated from structured values.
However, consider a dataset containing thousands of customer reviews. SQL can count the reviews or calculate average ratings, but identifying the most common complaints requires understanding the meaning of the text.
With AI.AGG, a developer can instruct BigQuery to summarize recurring complaints, identify common feature requests, or describe the overall sentiment of the reviews.
Typical use cases include:
- Summarizing customer feedback.
- Identifying recurring problems in application logs.
- Understanding support ticket themes.
- Analyzing user behavior from session descriptions.
- Summarizing collections of documents or supported images.
- Reviewing patterns in AI agent performance.
The function returns a STRING, making it suitable for producing summaries that can be stored, reviewed, or consumed by downstream reporting systems.
How AI.AGG Works
The function accepts two primary arguments: the input data and the instruction describing the desired aggregation.
AI.AGG(
input_expression,
'Natural-language instruction'
)
The input can be a string or a supported STRUCT containing text and supported image references.
Unlike a general-purpose AI generation function that produces a result for each input row, AI.AGG is designed to produce an aggregate result. It automatically batches input and combines intermediate results when necessary.
This approach is useful when the dataset is too large to fit into one model context window. However, automatic batching does not remove all practical limits. Large queries can still time out, incur substantial inference costs, or return partial results.
How to Use AI.AGG in BigQuery
Consider a table named customer_reviews containing the following columns:
product_id: Identifier of the reviewed product.review_text: Text written by the customer.rating: Numeric rating assigned by the customer.
The examples below assume that the table already exists in your BigQuery dataset.
Step 1: Summarize Customer Reviews
The first example generates an overall summary of customer feedback.
SELECT
AI.AGG(
review_text,
'Summarize the overall customer feedback. ' ||
'Identify recurring positive themes, common complaints, ' ||
'and frequently requested improvements. ' ||
'Keep the summary concise and distinguish recurring ' ||
'themes from isolated observations.'
) AS feedback_summary
FROM
`my_project.analytics.customer_reviews`
WHERE
review_text IS NOT NULL;
The query passes the review text to AI.AGG and describes the desired output through a natural-language instruction.
The instruction is important because a broad prompt such as "Analyze these reviews" leaves considerable room for interpretation. Specifying positive themes, complaints, and requested improvements makes the expected result clearer.
The output is a generated summary, not a deterministic SQL calculation. Validate important findings against the underlying records before using them to make business decisions.
Step 2: Generate Summaries for Individual Products
Often, a single summary is insufficient. Product managers may need to understand feedback separately for each product.
BigQuery can combine AI.AGG with GROUP BY to produce one aggregate result per product.
SELECT
product_id,
AI.AGG(
review_text,
'Summarize the most common customer complaints ' ||
'and positive feedback for this product. ' ||
'Identify recurring themes without inventing facts.'
) AS product_feedback
FROM
`my_project.analytics.customer_reviews`
WHERE
review_text IS NOT NULL
GROUP BY
product_id;
The query groups records by product_id and generates a summary for each group.
This approach is useful for product dashboards, customer experience reports, and feedback analysis across product categories.
For larger datasets, limit the number of groups processed in each query. Google's documentation recommends no more than 20 million input rows per query and no more than 1,000 distinct groups for best performance. Queries exceeding these recommendations may time out without returning results. <Cite refs={["turn105387search0"]}/>
Step 3: Filter the Input Before Aggregation
Not every record needs to be sent for analysis. Filtering unnecessary data before calling the AI function can reduce the amount of input processed.
SELECT
product_id,
AI.AGG(
review_text,
'Identify the main reasons customers report ' ||
'dissatisfaction. Summarize recurring issues.'
) AS complaint_summary
FROM
`my_project.analytics.customer_reviews`
WHERE
review_text IS NOT NULL
AND rating <= 2
GROUP BY
product_id;
This query focuses on reviews with ratings of two or lower.
Filtering makes the analysis more specific and can reduce unnecessary inference work. However, a low rating does not always explain the reason for dissatisfaction, so the generated summary should be checked against the original reviews.
AI.AGG Compared With Other BigQuery Functions
BigQuery offers several AI functions, but they are intended for different types of work.
| Function | Primary purpose | Typical output |
|---|
AI.AGG | Aggregate and summarize unstructured input | One string per group |
AI.GENERATE | Generate or analyze output for individual inputs | Result for each input row |
AI.GENERATE_TEXT | Generate text through a table-valued interface | A table containing generated results |
AI.CLASSIFY | Assign inputs to defined categories | Classification results |
AI.SCORE | Evaluate an input against a requested criterion | A score or evaluation result |
Use AI.AGG when the question concerns patterns across a collection of records. Use classification when records must be assigned to defined categories, and use general-purpose generation when the requirement concerns individual rows.
Choosing the appropriate function can simplify query design and reduce the need to implement custom batching logic.
Advantages and Disadvantages
Advantages
Natural-language aggregation: Developers can describe analytical goals without implementing every text-processing rule manually.
Automatic multi-level aggregation: The function can batch and combine intermediate results for datasets larger than a single model context window.
Integration with SQL: AI-powered summaries can be generated within existing BigQuery analytical workflows.
Support for multimodal input: Supported text and image data can be analyzed through the same aggregation approach.
Disadvantages
Inference costs: Processing large amounts of input through Gemini can be expensive.
Non-deterministic output: Generated summaries may vary and should not be treated as exact numerical calculations.
Execution limits: Large queries or excessive group counts can time out.
Potentially partial results: Failures involving model availability or quotas can produce partial output, while complete failure can result in NULL.
Best Practices for Production Workloads
1. Filter and Materialize the Input
Apply appropriate filters before running AI inference. For complex queries, materialize the intended input into a separate table before invoking AI.AGG.
This helps control which rows are processed and makes the input easier to inspect.
2. Write Specific Instructions
Tell the model what to summarize, what to exclude, and how to distinguish common themes from isolated observations.
For business-critical reporting, define a consistent output structure and validate the results against source data.
3. Monitor Inference Costs
AI inference costs depend on the data processed and the number of tokens sent to the model. Automatic batching can increase total token usage beyond the size of the original source data.
Use BigQuery's token-counting capabilities where appropriate and review inference costs before scaling a query to a large dataset.
4. Validate Important Conclusions
Do not use generated summaries as the sole source of truth for financial, compliance, or operational decisions.
Retain the underlying records and use conventional SQL calculations for exact counts, totals, and other measurable values.
5. Handle Errors and Partial Results
A successful query does not necessarily mean every model call completed successfully. Review the function's documented failure behavior and establish a process for validating generated output before downstream systems consume it.
Common Mistakes and Troubleshooting
The Query Returns NULL
Check whether the input contains valid data and whether the model calls completed successfully. The function can return NULL when all model calls fail or all input rows are invalid.
The Query Times Out
Reduce the number of input rows or distinct groups, and materialize the filtered dataset before running inference.
The Summary Contains Incorrect Details
Review the instruction and compare the output with the source records. Narrow the question and explicitly ask the model not to infer unsupported conclusions.
Costs Are Higher Than Expected
Check how many rows and tokens are being processed. Complex query plans can cause the number of rows reaching the model to differ from what you expect, so control the input through a materialized table when necessary.
Summary
BigQuery AI.AGG is now generally available, giving developers a practical way to summarize and analyze unstructured data through SQL. It supports natural-language aggregation, automatic multi-level processing, and grouping, making it useful for customer feedback, application logs, and document analysis.
The function is most valuable when an analytical question requires understanding the meaning of many records rather than calculating a conventional numeric aggregate.
For production use, filter the input carefully, write precise instructions, monitor inference costs, and validate generated summaries against the original data. Combining AI-powered analysis with traditional SQL provides a more reliable foundation for large-scale analytics.
Join the conversation! Your thoughts help the community grow.