This page describes how to use natural language in SQL operators using the
operator functions provided by the AlloyDB AI query engine. You can use
the ai.if, Join with ai.if, ai.score, and
ai.generate operators to combine natural language with SQL queries.
To use instructions on this page, you must have an understanding of AlloyDB for PostgreSQL and be familiar with generative AI concepts.
AlloyDB reserves the ai schema, and tries to
create this schema when you install the google_ml_integration
extension. If the schema creation fails, then use the functions with the same
name in the google_ml schema.
Before you begin
- Request access to use SQL operators with natural language and wait until you receive the enablement confirmation before you follow the instructions on this page.
Use filters and joins in your queries
To evaluate whether a condition stated in natural language is met, use the ai.if/google_ml.if operator . The function returns boolean true or false value, and returns NULL if output isn't clearly detected.
The following sample query shows how to find the restaurant reviews that mentions parking space.
SELECT review AS talks_about_parking
FROM user_reviews
WHERE
ai.if(
prompt => 'the following review talks about parking at the restaurant. review: ' || review);
Perform a join operation
To perform a join operation, use the ai.if/google_ml.if operator with join. The following sample query finds the number of reviews that mention each menu item from the restaurant menu.
SELECT item_name, COUNT(*)
FROM menu_items JOIN user_reviews
ON ai.if(
prompt => 'Does the following user review talk about the menu item mentioned ? review: ' || user_reviews.review_text || ' menu item: ' || item_name)
GROUP BY item_name;
Score your query results
To order a list of items in a query based on a semantic criteria stated using natural language, use the ai.rank/google_ml.rank operator
The following sample query gets the top 20 most positive restaurant reviews. It orders the response based on the criteria set in the ai.rank/google_ml.rank function call.
SELECT review AS top20
FROM user_reviews
ORDER BY ai.rank('Score the following review according to these rules: score of 8 to 10 if the review says the food is excellent, 4 to 7 if the review says the food is and 1 to 3 if the review says the food is not good. Here is the review: ' || review)
LIMIT 20;
Generate text for SQL operators with natural language
To generate text based on prompts specified in natural language, use the ai.generate/google_ml.generate function.
The following query generates a summary of each user review based on the criteria stated in the ai.generate function call.
SELECT
ai.generate(
prompt => 'Summarize the review in 20 words or less. Review: ' || review_text) AS review_summary
FROM user_reviews;