DocsICP FilingConsole
Documentation
Home Page Hologres User Guide AI features AI models and functions AI functions AI_FILTER

AI_FILTER

Updated at:
Copy as MD
Product
My Favorites

AI_FILTER is a Hologres AI function that evaluates text or a file as a true-or-false semantic predicate directly in SQL and returns a BOOLEAN result. This topic describes the limits, syntax, and usage examples of AI_FILTER.

Overview

AI_FILTER wraps the semantic reasoning of a large language model in a standard boolean function. You can use it anywhere a boolean value is accepted, such as a SELECT list, a WHERE clause, or a JOIN ON condition, without writing a UDF or integrating an external service.

Typical scenarios include:

  • Filtering reviews or feedback by sentiment or intent, such as whether a customer is satisfied.

  • Detecting non-compliant content, spam, or sensitive information.

  • Joining tables on semantics when no foreign key relationship exists.

  • Recognizing image content, such as whether an image contains a cat.

Limits

  • Version requirement: Only Hologres V4.2.12 and later are supported.

  • A managed model must be deployed first: Deploy a managed model in the Hologres console before you use this function. For more information, see Managed models.

  • The return_error_details parameter: You can either omit this parameter or set it to TRUE. If you omit it, a row that fails returns NULL. If you set it to TRUE, the function returns an OBJECT in the form {"value": ..., "error": ...}. Setting it to FALSE is not accepted and raises an error that directs you to the overload without this parameter.

  • Row-level fault tolerance: When you process multiple rows, a row that fails returns NULL and the query continues.

  • Non-deterministic results: The boolean result comes from the semantic judgment of the model. The same input may produce different results across models or model versions, so identical results are not guaranteed.

Syntax

-- Syntax 1: evaluate an input text as a boolean predicate
AI_FILTER( <model_name>, <input> [, <return_error_details> ] )

-- Syntax 2: evaluate a single file, such as an image, as a boolean predicate
AI_FILTER( <model_name>, <predicate>, <file> [, <return_error_details> ] )

-- Syntax 3: use a PROMPT template to assemble multiple columns or mixed text and file input
AI_FILTER( <model_name>, PROMPT('<template_string>', <col_1>, ... ) [, <return_error_details> ] )

Parameters

Parameter

Description

Required

model_name

The model name. Data type: TEXT.

Yes

input

The statement to evaluate. Data type: TEXT. The function automatically prepends a prompt that asks the model to judge whether the statement is TRUE or FALSE, and the model returns the boolean result.

Yes

predicate

The instruction that describes how to classify file as a boolean. Data type: TEXT. Example: 'Does the image contain a cat?'.

Yes

file

The file column to evaluate. Data type: FILE. You can pass an image file that is provided by to_file() or by an object table.

Yes

PROMPT('<template_string>', <col_1>, ...)

Use PROMPT() to assemble the input for complex prompts, especially when the prompt spans multiple columns or mixes text with files. It supports formatting for both text and FILE values.

Yes

return_error_details

Specifies whether to return error details when a row fails. Data type: BOOLEAN. If you omit this parameter, a failed row returns NULL. If you set it to TRUE, the function returns an OBJECT in the form {"value": ..., "error": ...}. Setting it to FALSE is not accepted and raises an error.

No

Error behavior

By default, if AI_FILTER cannot process a row, that row returns NULL and the query continues without interruption. The return value depends on the return_error_details parameter.

Value of return_error_details

Return value

Description

Omitted or TRUE, and the call succeeds

A BOOLEAN value of true or false, or an OBJECT in the form {"value": bool, "error": null}

A normal result.

Omitted, and the call fails

NULL

A row-level error. The query continues.

TRUE, and the call fails

{"value": null, "error": "<msg>"}

The error details are returned as an OBJECT.

FALSE

An error is raised

This value is not supported. The error directs you to the overload without this parameter.

The following error is returned when you pass FALSE:

ERROR: ai_filter: return_error_details must be TRUE when specified;
       use ai_filter(model_name, input) for BOOLEAN output
HINT:  Call ai_filter(model_name, input) to get a BOOLEAN result on error (NULL).

Examples

All of the following examples run on the Hologres managed model qwen3.8-max.

Example 1: Apply AI_FILTER to input text

SELECT ai_filter('qwen3.8-max', 'The sky is blue');

The following result is returned:

+------+
| ?    |
+------+
| t    |
+------+

Example 2: Filter semantically in a WHERE clause

Return only the order comments that express a positive opinion.

SELECT order_id, order_comments
FROM (VALUES
  (1, 'Great quality and fast delivery. Very satisfied.'),
  (2, 'The package was damaged and support was rude.'),
  (3, 'Just okay, good enough for now.')
) AS orders(order_id, order_comments)
WHERE ai_filter('qwen3.8-max', 'Does the customer like this product: ' || order_comments);

The following result is returned:

+----------+--------------------------------------------------+
| order_id | order_comments                                   |
+----------+--------------------------------------------------+
|        1 | Great quality and fast delivery. Very satisfied. |
+----------+--------------------------------------------------+

Example 3: Assemble the input by using the PROMPT function

SELECT ai_filter(
  'qwen3.8-max',
  prompt('Is {0} described as {1}?', 'wireless mouse', 'a black ergonomic mouse')
) AS consistent;

The following result is returned:

consistent
---------
f

Example 4: Join tables on semantics

The ticket table has no department ID, so a conventional foreign key join is not possible. You can use AI_FILTER to decide whether a ticket belongs to a department and use that decision as the join condition.

SELECT t.ticket_id, t.issue_text, d.department_name
FROM (VALUES
  (201, 'My phone screen is cracked. I want to request warranty repair.'),
  (202, 'The payment went through but the order status has not been updated.'),
  (203, 'I cannot receive the SMS verification code when I sign in.')
) AS t(ticket_id, issue_text)
JOIN (VALUES
  ('After-sales service'),
  ('Order operations'),
  ('Account support')
) AS d(department_name)
ON ai_filter(
  'qwen3.8-max',
  prompt('Should the following user issue be handled by "{0}"? Decide based on semantics only. User issue: {1}',
         d.department_name, t.issue_text)
);

The following result is returned:

ticket_id | issue_text                                                          | department_name
----------|---------------------------------------------------------------------|--------------------
      201 | My phone screen is cracked. I want to request warranty repair.      | After-sales service
      202 | The payment went through but the order status has not been updated. | After-sales service
      202 | The payment went through but the order status has not been updated. | Order operations
      203 | I cannot receive the SMS verification code when I sign in.          | Account support

Example 5: Apply AI_FILTER to an image file

SELECT id,
       ai_filter(
         'qwen3.8-max',
         'Does the image contain a cat?',
         to_file(img_path, 'oss-cn-hangzhou-internal.aliyuncs.com', 'acs:ram::<ACCOUNT_ID>:role/<ROLE_NAME>')
       ) AS has_cat
FROM (VALUES
  (1, 'oss://my-bucket/cat.png'),
  (2, 'oss://my-bucket/dog.png')
) AS images(id, img_path);

The following result is returned:

+----+---------+
| id | has_cat |
+----+---------+
|  1 | t       |
|  2 | f       |
+----+---------+

Example 6: Return error details

Set return_error_details to TRUE to return an OBJECT result.

SELECT ai_filter('qwen3.8-max', 'Water boils at 100 degrees Celsius', TRUE) AS result;

The following result is returned:

+----------------------------------+
| result                           |
+----------------------------------+
| {"value": true, "error": null}   |
+----------------------------------+
Note

To obtain a BOOLEAN result, omit the return_error_details parameter. Passing FALSE raises an error.

Previous：AI_GEN_STRUCTUREDNext：Long memory service