sqlalchemy JSONB jsonb_to_recordset

Question

i.m trying to convert sql statement to sqlalchemy ORM statement (expressions on jsonB) but in sqlalchemy i cannot find hot to use JSONB jsonb_to_recordset function

Here my expression in SQL (work perfectly)

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';

In sqlalchemy with text i did it (it works)

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;""")

In sqlalchemy ORM i try code below (but code is incorrect)

 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'))

How can i do it in sqlalchemy ORM?

Answer 1

In SQLAlchemy 1.4 you can build the query without having to resort to using 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')