'Count number of items in a column using Sqlalchemy

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

pythonpostgresqlsqlalchemyflask-sqlalchemy


Solution 1:[1]

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)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 snakecharmerb

Related Questions