尝试将横向SQL查询转换为SQLA ORM/Core API调用

Question

我将名称翻译存储在JSONB字段中，格式为{lang-code: translation}（来自sqlalchemy utils）。我使用jsonb_each_text()来检索查询中的名称。下面是我的模型课

import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB

from base import BaseModel


class Person(BaseModel):
    __tablename__ = 'person'

    id = sa.Column(sa.Integer(), primary_key=True)
    first_name_translations = sa.Column(JSONB, nullable=True)
    last_name_translations = sa.Column(JSONB, nullable=True)
    other_name_translations = sa.Column(JSONB, nullable=True)

这就是我试图用SQLA表达的SQL：

SELECT
id,
first_name.value,
last_name.value,
other_name.value
FROM person
LEFT JOIN LATERAL jsonb_each(first_name_translations) AS first_name ON TRUE
LEFT JOIN LATERAL jsonb_each(last_name_translations) AS last_name ON TRUE
LEFT JOIN LATERAL jsonb_each(other_name_translations) AS other_name ON TRUE;

在检查了SO和其他地方之后，我得到了类似的信息：

subquery = Person.query.with_entities(
    func.jsonb_each_text(
        Person.first_name_translations
    ).alias('first_name')
).subquery().lateral()

# raises error about ambiguous FROM
Person.query.filter(
    # ...
).outerjoin(subquery, sa.true())

但是上面的操作失败了，SQLA发出了一条“不明确的FROM子句”类型的消息

Answer 1

我可以这样解决查询问题：

subquery = func.jsonb_each_text(Person.first_name_translations).lateral('first_name')
Person.query.filter(
    # ...
).outerjoin(subquery, sa.true())