SQLalchemy query get aggregate array of dicts

user_5316326 0
Viewed 2848

Is it possible to get an aggregate array containing dictionaries from an SQLAlchemy query? e.g.

session.query(
    Object.name,
    func.array_agg({Location.id: Location.name}).label('locations')
)\
    .join(Location)\
    .all()

So the expected result will be:

[
 ('Horizontal neutral circuit',
  [{143:'A5'},{145:'A8'},{765:'B12'}]),
 ('Fletcher, Lopez and Edwards',
  [{41:'A1'},{76:'B8'},{765:'B12'}]),
]
sqlalchemy postgresql python
1 Answers

I guess you could use json_build_object() to build your dictionaries:

from sqlalchemy.dialects import postgresql

session.query(
        Object.name,
        postgresql.array_agg(
            func.json_build_object(Location.id,
                                   Location.name)).label('locations'))\
    .join(Location)\
    .group_by(Object.name)\
    .all()

with the caveat that the keys will be strings, compared to your example's integer keys.

user_26816320
Related