尝试将 LATERAL 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())