如何在SQLAlchemy中查询存储为字符串的JSON属性？

问题背景 #

我有一个City表，结构如下：

City
id(Integer),
name(String),
metadata(String)

表中数据示例：

1, "London", "{'lat':51.5072, 'lon':0.1276, 'Mayor': 'Sadiq Khan', 'participating':True}",
2, "Liverpool", "{'lat':53.4084, 'lon':2.9916, 'Mayor': 'Joanne Anderson', 'participating':False}",
3, "Manchester", "{'lat':53.4808, 'lon':2.2426, 'Mayor': 'Donna Ludford', 'participating':True}"

metadata是字符串类型，内部是JSON格式。我需要筛选出participating值为True的记录，预期结果是London和Manchester的两条数据。

我尝试了以下代码：

db.session.query(City).filter(cast(eval(metadata['participating]), String) == True)

但报错：

NotImplementedError: Operator 'getitem' is not supported on this expression

解决方案 #

错误原因 #

不能在SQLAlchemy过滤条件里用eval()，因为eval()是Python本地函数，SQLAlchemy无法将其转换为数据库能执行的SQL语句，这才导致了报错。

具体解决方法 #

根据你使用的数据库类型，选择对应的方案：

1. 支持JSON函数的数据库（PostgreSQL/MySQL 5.7+等）

利用数据库原生的JSON处理函数，SQLAlchemy可以通过func调用这些函数：

• PostgreSQL 写法：
  先将字符串类型的metadata转为jsonb，再提取指定字段的值：

  from sqlalchemy import func
  
  results = db.session.query(City).filter(
      func.jsonb_extract_path_text(City.metadata, 'participating') == 'True'
  ).all()
  

• MySQL 写法：
  使用JSON_EXTRACT提取字段，注意布尔值在MySQL JSON中的存储形式：

  from sqlalchemy import func
  
  # 方法1：直接匹配数值型布尔值（MySQL中True存为1，False存为0）
  results = db.session.query(City).filter(
      func.json_extract(City.metadata, '$.participating') == 1
  ).all()
  
  # 方法2：提取后转字符串匹配
  results = db.session.query(City).filter(
      func.json_unquote(func.json_extract(City.metadata, '$.participating')) == 'True'
  ).all()
  

2. 通用字符串匹配法（不依赖数据库JSON函数）

如果你的数据库不支持JSON函数，可以用字符串包含匹配，但这种方法存在误匹配风险（比如字段中存在相似字符串时），仅作为临时方案：

results = db.session.query(City).filter(
    City.metadata.contains("'participating':True")
).all()

3. 最优方案：修改模型字段类型

如果数据库支持JSON类型，直接将metadata字段改为JSON类型，后续查询会更简洁：

from sqlalchemy import JSON

class City(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    metadata = db.Column(JSON)  # 替换原String类型为JSON

修改后直接通过键值查询即可：

results = db.session.query(City).filter(City.metadata['participating'] == True).all()

内容的提问来源于stack exchange，提问作者Gerry Volta