SQLAlchemy "WITH ORDINALITY"

user_3868743 0
Viewed 481

I would like to translate the following query with SQLAlchemy but I don't find anything on this topic in the doc.

SELECT * FROM table_persons, jsonb_array_elements(data->'persons') WITH ORDINALITY as persons (data, n);

Where the data column has JSONB type.

sqlalchemy postgresql python
2 Answers

Unfortunately support for this feature has been moved to 1.5 milestone only. Although there is a workaround, I decided to use a raw SQL to solve a similar problem.

user_124631970

Update as of July 2021

SQLAlchemy 1.4 introduced table-valued functions, supporting WITH ORDINALITY.

For the example in the question:

SELECT *
FROM
    table_persons
,   jsonb_array_elements(data->'persons') WITH ORDINALITY AS persons (data, n)

The corresponding SQLAlchemy code would be

person_elements = (
    func.jsonb_array_elements(table_persons.c.data["persons"])
    .table_valued("data", with_ordinality="n", name="persons")
    .render_derived()
)
query = select(table_persons, person_elements)

The call to render_derived is to list the individual column names in the alias.

user_26165770
Related