postgresql - 如何在sqlalchemy的ORM中使用postgresql的jsonb_each函数

Question

我正在尝试编写一个函数来通过 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 语句时，我已经无计可施了。

Answer 1

对于返回标量或单列集的函数，您只需使用 func.something.alias('x')和 column('x') .不幸的是，SQLAlchemy 不支持显式为列设置别名，因此处理返回多列组合的函数有点棘手。如果是 jsonb_each the default names are key and value ，所以你可以使用那些:

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)