Count number of items in a column using Sqlalchemy

user_18251536 0
Viewed 251
SELECT
  *, (SELECT count(1) FROM unnest(A.b)) AS cnt
FROM A

In Table A, I am trying to count the number of items in column b containing a list of items. The query above works, but I am trying to write a sqlalchemy query as below, but it doesn't work. How can I write the query in sqlalchemy?

session.query(A, func.count(func.unnest(A.b)).label('cnt'))

Thanks in advance! Have a nice day :)

flask-sqlalchemy sqlalchemy postgresql python
1 Answers

In core the query would be

# t is the Table
subq = (
    sa.select(sa.func.count())
    .select_from(sa.func.unnest(t.c.b))
    .scalar_subquery()
)
q = sa.select(t, subq.label('cnt')).select_from(t)

The ORM equivalent is

with Session() as s:
    subq = (
        s.query(sa.func.count())
        .select_from(sa.func.unnest(A.b))
        .scalar_subquery()
    )
    q = s.query(A, subq.label('cnt')).select_from(A)
user_53209060
Related