SqlAlchemy 字段为JSON类型查询

• 要求Mysql5.7+

• model

class SysTestModel(Base):
    __tablename__ = "sys_test_data"
    id = Column(BIGINT, primary_key=True)
    name = Column(String(64), comment="name")
    relation_dict = Column("relation_dict", JSON, comment="json数据")
    memo = Column(String(10))
    def __str__(self):
        return "<SysTestModel {}>".format(self.id)

• relation_dict保存数据结果为

[{"rel": "小明"}, {"rel": "123"}, {"rel": "小红"}]

• 实现查询

query = SysTestModel.query.filter(db.func.json_contains(SysTestModel.relation_dict, db.func.json_object("rel", "123")))

SqlAlchemy 字段为JSON类型查询