PostgreSQL JSONB更新报错：无法确定多态类型，求修复方案

问题场景 #

数据库中ti_report_staging表的table_ioc字段为JSONB类型，存储结构如下：

[
 {"id": 6, "tlp": "AMB", "type": "TTP", "value": "TYYY", "nature": "danger", "threat_actor": "Row"},
 {"id": 7, "tlp": "YELLOW", "type": "TTP", "value": "T888", "nature": "light", "threat_actor": "White"}
]

需要根据对象的id，更新对应元素的tlp、threat_actor、nature字段，但原代码执行时触发类型不匹配错误：

Database Error: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.DatatypeMismatchError'>: could not determine polymorphic type because input has type unknown

错误原因 #

1. 原SQL依赖数组索引定位元素，不符合"根据id更新"的需求，且数组索引会因元素顺序变化失效
2. 多层嵌套jsonb_set写法冗余，当参数为None时，to_jsonb(None)返回unknown类型，导致PostgreSQL无法推断字段类型
3. 未显式提交事务，可能导致更新不生效

修复方案 #

使用PostgreSQL的JSONB数组操作，通过id匹配目标元素，合并原对象与更新字段，重新聚合数组。同时处理参数为空的情况，避免类型错误。

修正后的代码 #

try:
    query = """
        UPDATE ti_report_staging
        SET table_ioc = (
            SELECT jsonb_agg(
                CASE WHEN elem->>'id' = :target_id THEN
                    elem || jsonb_build_object(
                        'tlp', coalesce(to_jsonb(:tlp), elem->'tlp'),
                        'nature', coalesce(to_jsonb(:nature), elem->'nature'),
                        'threat_actor', coalesce(to_jsonb(:threat_actor), elem->'threat_actor')
                    )
                ELSE elem END
            )
            FROM jsonb_array_elements(table_ioc) AS elem
        )
        WHERE pk = :pk;
    """

    # 注意：target_id需传入字符串类型，因为elem->>'id'返回字符串
    await session.execute(text(query), {
        "target_id": str(target_id),
        "tlp": data.get("tlp"),
        "nature": data.get("nature"),
        "threat_actor": data.get("threat_actor"),
        "pk": pk,
    })
    await session.commit()
    print("Updated IOC successfully.")
except Exception as e:
    print(f"Database Error: {str(e)}")
    await session.rollback()
    print("Rollback completed.")

关键说明 #

• 按id匹配元素：通过jsonb_array_elements展开数组，用CASE WHEN匹配目标id的元素，避免依赖数组索引
• 安全更新字段：用||操作符合并原对象与新键值对，自动覆盖指定字段；coalesce确保参数为None时保留原字段值，避免类型错误
• 重新聚合数组：用jsonb_agg将处理后的元素重新组合成JSONB数组，替换原字段
• 显式提交事务：添加await session.commit()确保更新生效

内容的提问来源于stack exchange，提问作者Shinu Antony