如何在SQLAlchemy中实现JSON字段的追加（含Upsert场景）

你的代码之所以会覆盖原有数据，是因为错误地使用了Python风格的字典+数组语法来尝试追加JSON数据，这在PostgreSQL的SQL层面无法实现数组追加逻辑，反而会生成错误的表达式导致覆盖。

修正方案 #

要实现JSON数组的追加，需要使用PostgreSQL原生的数组操作函数或运算符，结合SQLAlchemy的func来调用这些原生功能：

完整修正代码

from sqlalchemy import func, cast, JSONB
from sqlalchemy.dialects.postgresql import insert as pg_insert

# 构建插入语句
stmt = pg_insert(Users).values(
    userid=user.id,
    pricesjson=[{
        "product1": user.product1,
        "product2": user.product2,
        "product3": user.product3
    }],
    tsins=datetime.now()
)

# 构建Upsert语句，实现JSON数组追加
stmtUpsert = stmt.on_conflict_do_update(
    index_elements=[Users.userid],
    set_={
        # 使用PostgreSQL的array_append函数，将新的产品对象追加到原有数组末尾
        'pricesjson': func.array_append(
            Users.pricesjson,
            cast(
                {
                    "product1": user.product1,
                    "product2": user.product2,
                    "product3": user.product3
                },
                JSONB
            )
        ),
        'tsvar': datetime.now()
    }
)

关键说明 #

1. 使用PostgreSQL原生函数：func.array_append是直接调用PostgreSQL的array_append函数，专门用于向数组类型字段追加元素。
2. 类型匹配：将新的产品字典通过cast转换为JSONB类型，确保和字段类型一致（推荐数据库中pricesjson字段设为JSONB类型，性能更优）。
3. 替代方案（合并数组）：如果你的插入逻辑是传入一个数组，希望将新数组合并到原数组末尾，可以用||运算符：

   'pricesjson': Users.pricesjson || stmt.excluded.pricesjson
   

为什么原代码会覆盖 #

原代码中{"product1": ...} + cast(stmt.excluded.pricesjson, JSONB)的写法，在SQLAlchemy中会被解析为错误的表达式，最终导致数据库将新生成的字典/数组直接覆盖原有字段，而非追加。

内容的提问来源于stack exchange，提问作者Damiano Dotto