AI_GEN_STRUCTURED

模式一：json_schema（推荐）

通过标准 JSON Schema 精确定义输出结构，模型将严格按 schema 生成输出：

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

Schema 越精确（类型约束、enum 枚举、description 描述），输出质量越高。

模式二：json_object

仅保证输出为合法 JSON 对象，具体字段由提示词控制：

{
    "type": "json_object"
}

使用 json_object 时，需要在 message 提示词中明确说明期望的 JSON 字段和格式。

示例 1：从文本中提取结构化实体

SELECT ai_gen_structured(
    model_name => 'qwen3.7-max',
    message => '从以下文本中提取人员信息：张三是阿里巴巴的高级工程师，男，汉族，1990年3月15日出生，今年35岁',
    response_format => '{
        "type": "json_schema",
        "schema": {
            "type": "object",
            "properties": {
                "name": {"type": "string"},
                "age": {"type": "integer"},
                "sex": {"type": "string", "enum": ["男", "女"]},
                "birth_day": {"type": ["string", "null"], "format": "date"},
                "民族": {"type": "string", "enum": ["汉族", "蒙古族", "回族", "藏族", "维吾尔族"]}
            },
            "required": ["name", "age", "sex", "birth_day", "民族"],
            "additionalProperties": false
        }
    }'::json
);

输出：

{"name": "张三", "age": 35, "sex": "男", "birth_day": "1990-03-15", "民族": "汉族"}

示例 2：json_object 模式

SELECT ai_gen_structured(
    model_name => 'qwen3.7-max',
    message => '请以 JSON 格式返回杭州这座城市的信息，字段包括：province（所属省份）、is_municipality（是否直辖市，布尔值）。',
    response_format => '{"type": "json_object"}'::json
);

输出：

{"province": "浙江省", "is_municipality": false}

示例 3：多模态 — 从图片中提取结构化信息

SELECT ai_gen_structured(
    model_name => 'qwen3.7-plus',
    message => '识别这张驾驶场景图片，提取场景结构化信息。',
    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": "场景描述"},
                "time_of_day": {"type": "string", "enum": ["白天", "夜晚", "黄昏"]},
                "weather": {"type": "string"},
                "has_vehicle": {"type": "boolean"}
            },
            "required": ["scene", "time_of_day", "weather", "has_vehicle"],
            "additionalProperties": false
        }
    }'::json
);

示例 4：批量处理表数据

-- 假设 documents 表有 id 和 content 字段
SELECT id, ai_gen_structured(
    'qwen3.7-max',
    '提取人员信息：' || 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;

批量处理时建议使用 on_error => 'skip'，避免单行异常中断整个查询。