python-3.x - 如何用sqlalchemy查询jsonb数组

Question

我将数据存储在 jsonb 字段中，如下所示:

class Test(Base):
    __tablename__ = 'test'
    id = Column(Integer, primary_key=True)
    data = Column(JSONB)

在 data 列中有一个 json 格式:

{depth: [0.0, 0.01, 0.02, 0.03, 0.04, 0.05, 0.06]}

我想确定每条记录的最大深度，并在原始 SQL 中提出了以下查询来完成这项工作:

SELECT test.id, test.name,
  (SELECT max(elem::float)
   FROM jsonb_array_elements_text(test.data -> 'depth') As elem
   ) AS maxdepth
FROM test
ORDER BY maxdepth DESC

因为我在我的应用程序中使用 SQLAlchemy ORM，所以我想用 SQLAlchemy ORM 编写这个查询，但我想不出正确的形式。

我在想我需要这样的东西:

subq = session.query(
    func.max().label('maxdepth')).\
    select_from(func.jsonb_array_elements(Test.data['depth'])).\
    subquery()

stmnt = session.query(
    Test.id, subq.c.maxdepth).\
    order_by(subq.c.maxdepth)

但这显然行不通，因为我不知道如何从 jsonb_array_elements 提取的字段中查询

Answer 1

[注意:自 SQLAlchemy 1.0 起，2015 年 10 月 26 日。这可能会在未来的版本中发生变化]这些特殊的 PG 语法目前尚未内置到 SQLAlchemy 中，请参阅 https://bitbucket.org/zzzeek/sqlalchemy/issues/3566/figure-out-how-to-support-all-of-pgs#comment-22842678 中的配方这说明了您的查询。