python - 如何使用 SQLAlchemy 在 PostgreSQL JSON 字段中的 JSON 数组中选择包含给定值的记录?
问题描述
我在 PostgeSQL 数据库中有data
具有这种结构的 JSON 字段的项目(用户):
{
"department": [
"IT"
],
"competencies": [
"moderator",
"language_ru",
"language_en"
]
}
使用 flask-sqlalchemy 访问数据库。
from sqlalchemy.dialects.postgresql import JSON
class UserDBO(db.Model):
__tablename__ = 'user'
id = db.Column(db.BigInteger, primary_key=True)
display_name = db.Column(db.String(length=100), index=True),
email = db.Column(db.String(length=254), index=True)
phone = db.Column(db.String(length=16), index=True)
created = db.Column(db.DateTime)
data = db.Column(JSON, default={}, nullable=False)
我需要选择具有一定能力的用户。我为 sqlalchemy 查询尝试了以下过滤器(没有一个按预期工作,大多数返回错误):
from sqlalchemy.types import Unicode, String
from sqlalchemy import cast
from sqlalchemy import func
from sqlalchemy.dialects import postgresql
records = UserDBO.query.filter(
# func.json_contains(UserDBO.data['competencies'],cast('moderator', String)),
# UserDBO.data['competencies'].contains({'competencies':['moderator']}),
# UserDBO.data['competencies'].contains('moderator')
# UserDBO.data['competencies'].astext.cast(Unicode) == 'moderator',
# UserDBO.data['competencies'] == ['moderator'],
# ['moderator'] in UserDBO.data['competencies'],
# ['moderator'] in UserDBO.data['competencies'],
# UserDBO.data['competencies'] == cast('moderator', Unicode),
)
我需要选择与多种能力相匹配的项目,例如['moderator','language_en'] in UserDBO.data['competencies']
.
使用某种过滤器一一选择它们'moderator' in UserDBO.data['competencies']
也是可以的。
此外,通过创建适当的索引来优化此类查询也将非常有趣。
解决方案
推荐阅读
- c# - 在数据网格 wpf 中绑定嵌套列表
- django - 在单行中以多对多关系获取数据
- javascript - 错误:警告:在循环依赖中访问模块导出的不存在属性“findOne”
- node.js - 以编程方式在 mongodb 中插入 JSON 文件
- wordpress - 如何通过 npm 安装的 wordpress 脚本激活 GitHub 插件?
- javascript - 字符串在 javascript 中是如何工作的?
- r - 如何使用 R 中的绘图以仅 10 次方的对数刻度显示 y 轴
- node.js - 在 throw new Error('record not found') 语句中出现 Nodejs 和 Mongoose 错误
- python - Django 创建一个 models.ManyToMany 和 models.ForeignKey 到同一张表
- insert - 试图用多个 WHEN THEN 编写一个 INSERT WHERE NOT EXISTS