python - 在 sqlalchemy 查询中使用 jsonb_array_elements

Question

我正在使用 SQLAlchemy ORM 并试图弄清楚如何生成 PostgreSQL 查询，大致如下:

SELECT
    payments.*
FROM
    payments,
    jsonb_array_elements(payments.data #> '{refunds}') refunds
WHERE
    (refunds ->> 'created_at')
        BETWEEN '2018-12-01T19:30:38Z' AND '2018-12-02T19:30:38Z';

尽管包含开始日期且不包含结束日期。

我已经能够接近:

refundsInDB = db_session.query(Payment).\
    filter(Payment.data['refunds', 0, 'created_at'].astext >= startTime,
           Payment.data['refunds', 0, 'created_at'].astext < stopTime ).\
    all()

但是，只有退款(JSONB 数据中的嵌套数组)是 {'refunds':[]} 列表中的第一个元素时才有效，而上面的 SQL 查询无论退款列表中的位置如何，都将起作用。

经过一番搜索后，看起来旧的 SQLAlchemy github issue 中有一些临时食谱。 ，其中一篇讨论了在查询中使用 jsonb_array_elements，但我还无法完全使其按照我想要的方式工作。

如果它有帮助的话，我的 Payment.data JSONB 与 Payment 对象完全相同 from the Square Connect v1 API我正在尝试使用 refunds 对象的嵌套 refunds 列表中找到的 created_at 日期来搜索数据。

Answer 1

使用Query.select_from()和一个function expression alias执行查询:

# Create a column object for referencing the `value` attribute produced by
# the set of jsonb_array_elements
val = db.column('value', type_=JSONB)

refundsInDB = db_session.query(Payment).\
    select_from(
        Payment,
        func.jsonb_array_elements(Payment.data['refunds']).alias()).\
    filter(val['created_at'].astext >= startTime,
           val['created_at'].astext < stopTime ).\
    all()

请注意，取消嵌套 jsonb 数组并基于它进行过滤可能会为每个 Payment 生成多行，但在查询单个实体时，SQLAlchemy 将仅返回不同的实体。