SQLAlchemy filter_by json column

Question

我有这样的模型：

class Customer(Base):
__tablename__ = 'customers'
uuid = database.Column(database.String, primary_key=True)
activity_type = database.Column(database.String)
activity = database.Column(database.JSON)

我需要filter_by activity_type，还需要这样的活动：

@staticmethod
def create_if_not_exist(customer):
""" Create customer if not exists """
if Customer.query.filter_by(
activity_type=customer.activity_type,
activity=customer.activity
).first() is None:
customer.save()
return customer.uuid

如果不适用于 json 列活动。有人可以帮我如何filter_by json 列吗？

Answer 1

一个PostgreSQL特定的解决方案是利用PostgreSQLs JSONB列：

from sqlalchemy.dialects.postgresql import JSONB
class Customer(Base):
__tablename__ = 'customers'
uuid = database.Column(database.String, primary_key=True)
activity_type = database.Column(database.String)
activity = database.Column(JSONB)

然后，原始查询应正确计算。