我正在尝试编写一个函数来通过 sqlalchemy 计算 postgres 中嵌套 json 值的平均值。我试图平均的值在统计表中，有一个分数列，其中包含这样的 json 字典（过滤到相关结构）：{1: {'score': 0.0}, 2: {'score' ：0.0} ...}。
用 postgres 编写的查询如下所示：
SELECT *, avg((v->>'score')::float) AS average_score
FROM lms.statistics, jsonb_each(statistics.scores) js(k, v)
WHERE jsonb_typeof(scores) != 'null'
GROUP BY statistics.id
我主要将它转换为以下 sqlalchemy 代码：
(
session.query(Statistics)
.add_columns(literal_column("avg((v->>'score')::float)").label('average_score'))
.filter(literal("jsonb_typeof(statistics.scores != 'null'"))
.group_by(Statistics.id)
).all()
但是，无论我尝试做什么，sqlalchemy 都不允许我包含此查询所依赖的 jsonb_each。我什至尝试重构查询以使用显式连接，而 sqlalchemy 的 .join 不会接受literal_column、文本或任何带有外部连接或指定虚假连接条件的技巧。当必须有一个 sqlalchemy 标准来将纯文本查询插入到 FROM 或 JOIN 语句中时，我已经竭尽全力试图欺骗它。
对于返回标量或单列集的函数，您只需使用func.something.alias('x')and column('x')。不幸的是，SQLAlchemy 不支持显式地为列设置别名，因此处理返回多列组合的函数有点棘手。如果jsonb_each 默认名称是keyandvalue，那么您可以使用这些：
v = column('value', type_=JSONB)
score = v['score'].astext.cast(Float)
session.query(Statistics,
func.avg(score).label('average_score')).\
select_from(Statistics,
func.jsonb_each(Statistics.scores).alias()).\
filter(func.jsonb_typeof(Statistics.scores) != 'null').\
group_by(Statistics.id)
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句