AI_GEN_STRUCTURED-Hologres(Hologres)-阿里云帮助中心

Prerequisites

Your Hologres instance runs V4.2.4 or later.
The required model is deployed in the Hologres console. For more information, see Managed models.

Syntax

Extract structured information from text

ai_gen_structured(
    model_name      TEXT,
    message         TEXT,
    response_format JSON,
    on_error        TEXT DEFAULT 'capture',
    params          JSON DEFAULT '{}'::json
) RETURNS JSON

Extract structured information from multimodal input such as images or videos

ai_gen_structured(
    model_name      TEXT,
    message         TEXT,
    input_file      FILE,
    response_format JSON,
    on_error        TEXT DEFAULT 'capture',
    params          JSON DEFAULT '{}'::json
) RETURNS JSON

Parameters

Parameter	Type	Required	Default	Description
model_name	TEXT	Yes	-	The model name, such as `qwen3.7-max` or `qwen3.7-plus`. Deploy the model in advance. For more information, see Managed models.
message	TEXT	Yes	-	The prompt sent to the model.
input_file	FILE	No	-	The input file. Supported only by the multimodal overload. Construct it with `to_file(uri, endpoint, role_arn)`. For more information, see Construct a multimodal input file in this topic.
response_format	JSON	Yes	-	The output format definition. Two modes are supported: `json_schema` and `json_object`. For more information, see response_format modes in this topic.
on_error	TEXT	No	'capture'	The row-level error handling policy. It governs only errors that occur during the model inference stage, such as the model returning invalid JSON. Valid values: `'capture'` (default): When inference fails, the error message is returned as the JSON result and the query continues. `'skip'`: When inference fails, the row returns NULL and the query continues. `'abort'`: When inference fails, an exception is thrown and the entire SQL statement is aborted. For batch processing, use `'skip'` or `'capture'` to prevent a single failed row from aborting the entire query. Note The following usage-level errors are not governed by on_error and always throw an exception directly: the model name does not exist; the input file does not exist (OSS 404); SQL syntax or logic errors.
params	JSON	No	'{}'	Additional parameters passed in JSON format, such as `{"temperature": 0.1}`.

Return value

Returns a value of the JSON type whose content conforms to the schema defined in response_format.

response_format modes

Mode 1: json_schema (recommended)

Define the output structure precisely with a standard JSON Schema. The model generates output that strictly follows the schema:

{
    "type": "json_schema",
    "schema": {
        "type": "object",
        "properties": {
            "field_name": {"type": "string"},
            "field_age": {"type": "integer"}
        },
        "required": ["field_name", "field_age"],
        "additionalProperties": false
    }
}

The more precise the schema (type constraints, enum values, and description), the higher the output quality.

Mode 2: json_object

Guarantees only that the output is a valid JSON object. The specific fields are controlled by the prompt:

{
    "type": "json_object"
}

When you use json_object, describe the expected JSON fields and format explicitly in the message prompt.

Examples

Example 1: Extract structured entities from text

SELECT ai_gen_structured(
    model_name => 'qwen3.7-max',
    message => 'Extract personal information from the following text: Zhang San is a senior engineer at Alibaba, male, Han ethnicity, born on March 15, 1990, 35 years old this year.',
    response_format => '{
        "type": "json_schema",
        "schema": {
            "type": "object",
            "properties": {
                "name": {"type": "string"},
                "age": {"type": "integer"},
                "sex": {"type": "string", "enum": ["male", "female"]},
                "birth_day": {"type": ["string", "null"], "format": "date"},
                "ethnicity": {"type": "string"}
            },
            "required": ["name", "age", "sex", "birth_day", "ethnicity"],
            "additionalProperties": false
        }
    }'::json
);

Output:

{"name": "Zhang San", "age": 35, "sex": "male", "birth_day": "1990-03-15", "ethnicity": "Han"}

Example 2: json_object mode

SELECT ai_gen_structured(
    model_name => 'qwen3.7-max',
    message => 'Return information about the city of Hangzhou in JSON format, with the fields province and is_municipality (a boolean).',
    response_format => '{"type": "json_object"}'::json
);

Output:

{"province": "Zhejiang", "is_municipality": false}

Example 3: Multimodal - extract structured information from an image

SELECT ai_gen_structured(
    model_name => 'qwen3.7-plus',
    message => 'Recognize this driving-scene image and extract structured scene information.',
    input_file => to_file(
        'oss://your-bucket/path/to/image.jpg',
        'oss-cn-hangzhou-internal.aliyuncs.com',
        'acs:ram::your_account_id:role/your_role'
    ),
    response_format => '{
        "type": "json_schema",
        "schema": {
            "type": "object",
            "properties": {
                "scene": {"type": "string", "description": "Scene description"},
                "time_of_day": {"type": "string", "enum": ["day", "night", "dusk"]},
                "weather": {"type": "string"},
                "has_vehicle": {"type": "boolean"}
            },
            "required": ["scene", "time_of_day", "weather", "has_vehicle"],
            "additionalProperties": false
        }
    }'::json
);

Example 4: Batch processing of table data

-- Assume the documents table has id and content columns
SELECT id, ai_gen_structured(
    'qwen3.7-max',
    'Extract personal information: ' || content,
    '{"type":"json_schema","schema":{"type":"object","properties":{"name":{"type":"string"},"age":{"type":"integer"},"city":{"type":"string"}},"required":["name","age","city"],"additionalProperties":false}}'::json,
    'skip',
    '{"temperature":0.1}'::json
) AS extracted_info
FROM documents
ORDER BY id;

For batch processing, use on_error => 'skip' to prevent a single failed row from aborting the entire query.

Construct a multimodal input file

Use the to_file() function to construct a parameter of the FILE type:

to_file(
    object_uri  TEXT,   -- OSS object path, such as 'oss://bucket/path/file.jpg'
    endpoint    TEXT,   -- OSS endpoint, such as 'oss-cn-hangzhou-internal.aliyuncs.com'
    role_arn    TEXT    -- RAM role ARN, such as 'acs:ram::account_id:role/role_name'
)

The instance must be authorized to read the target OSS bucket through the corresponding role ARN.

Usage notes

Schema design: The more precise the JSON Schema in response_format, the higher the output quality. Add a description to each field, use enum for values from a fixed set, and use the ["string", "null"] type for optional fields.
Batch processing: When you extract from a table in batches, use on_error => 'skip' or 'capture' to prevent a single failed row from aborting the entire query.
Model selection: For text extraction, use qwen3.7-max (quality first) or qwen3.7-plus (cost-effective). For multimodal scenarios, use a model with vision understanding, such as qwen3.7-plus.
Non-JSON output: If you want non-JSON text output, use the ai_gen function.
The params parameter: In structured output mode, the engine overrides some model parameters (such as max_tokens) to ensure the JSON output is complete. Parameters such as temperature still take effect.