sqlalchemy JSONB jsonb_to_recordset

Question

我正在尝试将sql语句转换为sqlalchemy ORM语句( JSONB上的表达式)，但在sqlalchemy中我找不到热来使用JSONB jsonb_to_recordset函数

下面是我在SQL中的表达式(可以完美地工作)

select title, items.name, items.exist_data, items.found
        from loaninformation,jsonb_to_recordset(loaninformation.general_info) 
                                                as items(name text, exist_data text, found bool)
        where title='r12' and items.name='Investor';

在带有文本的sqlalchemy中，我做到了(它有效)

s = text("""select title, items.name, items.exist_data, items.found
                                        from loaninformation,jsonb_to_recordset(loaninformation.general_info)
                                                as items(name text, exist_data text, found bool)
                                                    where title=:title and items.name=:investor_name;""")

在sqlalchemy ORM中，我尝试了下面的代码(但代码不正确)

 query = session.query(items.name, items.exist_data, items.found sqlalchemy.func.jsonb_to_recordset(LoanInformation.general_info).\
            label("items"(name text, exist_data text, found bool))
    query = query.filter(and_(LoanInformation.title=='r12', LoanInformation.items.name=='Investor'))

我如何在sqlalchemy ORM中做到这一点？

Answer 1

在SQLAlchemy 1.4中，无需使用text即可构建查询

from sqlalchemy import func, column, Text, Boolean

items = func.jsonb_to_recordset(LoanInformation.general_info).\ 
    table_valued(
        column("name", Text),
        column("exist_data", Text),
        column("found", Boolean)).\
    render_derived(with_types=True)

query = session.query(
        LoanInformation.title,
        items.c.name,
        items.c.exist_data,
        items.c.found).\
    filter(LoanInformation.title == 'r12',
           items.c.name == 'Investor')