Category: Managed AI Function (Preview)

Description

Filters data based on semantic meaning using natural language conditions. Returns a BOOL value indicating whether the input satisfies the condition. Designed for use in WHERE and JOIN clauses to enable semantic filtering without exact keyword matching.

Use Cases

Content Filtering: Find articles related to specific topics without exact keyword matches

Security: Identify potential phishing emails or suspicious content

Product Search: Filter products by semantic attributes ("suitable for outdoor use")

Entity Resolution: Perform semantic joins between different datasets

Data Quality: Filter records meeting semantic criteria

Syntax

AI.IF(
  ('CONDITION', INPUT),
  [, connection_id => 'CONNECTION' ]
  [, endpoint => 'ENDPOINT' ]
)

AI.IF(
  ('CONDITION', INPUT),
  [, connection_id => 'CONNECTION' ]
  [, endpoint => 'ENDPOINT' ]
)

AI.IF(
  ('CONDITION', INPUT),
  [, connection_id => 'CONNECTION' ]
  [, endpoint => 'ENDPOINT' ]
)

Parameters

CONDITION: Natural language description of the filtering criteria

INPUT: Text data from columns OR ObjectRefRuntime values for multimodal data

CONNECTION (optional): Connection ID format: project.location.connection_id

ENDPOINT (optional): Gemini model endpoint (auto-selected if omitted)

Code Examples

Example 1: Semantic Filtering in WHERE Clause

SELECT 
  title,
  body
FROM news_articles
WHERE AI.IF(
  ('This article discusses climate change.', body),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 20

SELECT 
  title,
  body
FROM news_articles
WHERE AI.IF(
  ('This article discusses climate change.', body),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 20

SELECT 
  title,
  body
FROM news_articles
WHERE AI.IF(
  ('This article discusses climate change.', body),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 20

Example 2: Phishing Detection

SELECT 
  email_id,
  subject,
  sender,
  body
FROM emails
WHERE AI.IF(
  ('This email is a phishing attempt.', CONCAT(subject, ' ', body)),
  connection_id => 'us.my_vertex_connection'
)
ORDER BY received_date DESC

SELECT 
  email_id,
  subject,
  sender,
  body
FROM emails
WHERE AI.IF(
  ('This email is a phishing attempt.', CONCAT(subject, ' ', body)),
  connection_id => 'us.my_vertex_connection'
)
ORDER BY received_date DESC

SELECT 
  email_id,
  subject,
  sender,
  body
FROM emails
WHERE AI.IF(
  ('This email is a phishing attempt.', CONCAT(subject, ' ', body)),
  connection_id => 'us.my_vertex_connection'
)
ORDER BY received_date DESC

Example 3: Optimized Query with Traditional Filters First

-- BigQuery runs cheap filters first, then AI filters on the subset
SELECT 
  product_id,
  name,
  description
FROM products
WHERE category = 'outdoor'  -- Traditional filter (cheap)
  AND price < 100           -- Traditional filter (cheap)
  AND AI.IF(                -- AI filter (expensive, runs last)
    ('This product is suitable for camping.', description),
    connection_id => 'us.my_vertex_connection'
  )

-- BigQuery runs cheap filters first, then AI filters on the subset
SELECT 
  product_id,
  name,
  description
FROM products
WHERE category = 'outdoor'  -- Traditional filter (cheap)
  AND price < 100           -- Traditional filter (cheap)
  AND AI.IF(                -- AI filter (expensive, runs last)
    ('This product is suitable for camping.', description),
    connection_id => 'us.my_vertex_connection'
  )

-- BigQuery runs cheap filters first, then AI filters on the subset
SELECT 
  product_id,
  name,
  description
FROM products
WHERE category = 'outdoor'  -- Traditional filter (cheap)
  AND price < 100           -- Traditional filter (cheap)
  AND AI.IF(                -- AI filter (expensive, runs last)
    ('This product is suitable for camping.', description),
    connection_id => 'us.my_vertex_connection'
  )

Example 4: Semantic JOIN

-- Find semantically matching products across two catalogs
SELECT 
  a.product_id AS catalog_a_id,
  a.product_name AS catalog_a_name,
  b.product_id AS catalog_b_id,
  b.product_name AS catalog_b_name
FROM catalog_a AS a
CROSS JOIN catalog_b AS b
WHERE AI.IF(
  ('These two products are the same item.', 
   CONCAT('Product A: ', a.product_name, ' ', a.description, 
          ' | Product B: ', b.product_name, ' ', b.description)),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 100

-- Find semantically matching products across two catalogs
SELECT 
  a.product_id AS catalog_a_id,
  a.product_name AS catalog_a_name,
  b.product_id AS catalog_b_id,
  b.product_name AS catalog_b_name
FROM catalog_a AS a
CROSS JOIN catalog_b AS b
WHERE AI.IF(
  ('These two products are the same item.', 
   CONCAT('Product A: ', a.product_name, ' ', a.description, 
          ' | Product B: ', b.product_name, ' ', b.description)),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 100

-- Find semantically matching products across two catalogs
SELECT 
  a.product_id AS catalog_a_id,
  a.product_name AS catalog_a_name,
  b.product_id AS catalog_b_id,
  b.product_name AS catalog_b_name
FROM catalog_a AS a
CROSS JOIN catalog_b AS b
WHERE AI.IF(
  ('These two products are the same item.', 
   CONCAT('Product A: ', a.product_name, ' ', a.description, 
          ' | Product B: ', b.product_name, ' ', b.description)),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 100

Example 5: Image Filtering

SELECT 
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS image_url
FROM product_images
WHERE AI.IF(
  ('This image contains a person wearing red.', OBJ.GET_ACCESS_URL(ref, 'r')),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 10

SELECT 
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS image_url
FROM product_images
WHERE AI.IF(
  ('This image contains a person wearing red.', OBJ.GET_ACCESS_URL(ref, 'r')),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 10

SELECT 
  STRING(OBJ.GET_ACCESS_URL(ref,'r').access_urls.read_url) AS image_url
FROM product_images
WHERE AI.IF(
  ('This image contains a person wearing red.', OBJ.GET_ACCESS_URL(ref, 'r')),
  connection_id => 'us.my_vertex_connection'
)
LIMIT 10

Data Output Examples

Climate Change Articles

Phishing Emails

Best Practices

Place traditional filters first: Let BigQuery optimize by running cheap filters before expensive AI filters

Be specific in conditions: Clear, specific conditions yield better results

Use for semantic matching: Ideal when exact keywords won't work

Combine with LIMIT: Control costs by limiting result sets

Test conditions first: Validate AI.IF behavior with sample data

When to Use

✅ Use AI.IF for semantic filtering beyond keyword matching

✅ Use in WHERE clauses to filter based on meaning

✅ Use in JOIN conditions for entity resolution

✅ Use when traditional pattern matching is insufficient

Alternatives

Traditional WHERE: For exact matches or pattern matching (cheaper)

LIKE/REGEXP: For pattern-based filtering

AI.CLASSIFY: When you need to categorize into multiple groups

AI.GENERATE_BOOL: When you need custom prompt control

Platform Support

Regions: All Gemini-supported regions + US/EU multi-regions

Preview Status: Currently in Preview (Pre-GA)

Cost: Charged per Vertex AI API call

Optimization: BigQuery query planner optimizes to minimize AI function calls

Returns

BOOL value: true if input satisfies the condition, false otherwise. Returns NULL if the Vertex AI call fails.