SQLAlchemy, PostgreSQL and array_agg: how to select elements from array_agg?

Question

I want to use array_agg in a subquery, and then use the aggregated data for this array index in my main query, however after trying in different ways, I really don't understand how to do this; can someone explain why in the example below I get a series of None values ​​instead of the first category in an array?

I understand that the following simplified example can be done without performing SELECT on the [i] array, but it will explain the nature of the problem:

from sqlalchemy import Integer from sqlalchemy.dialects.postgres import ARRAY prods = ( session.query( Product.id.label('id'), func.array_agg(ProductCategory.id, type_=ARRAY(Integer)).label('cats')) .outerjoin( ProductCategory, ProductCategory.product_id == Product.id) .group_by(Product.id).subquery() ) # Confirm that there categories: [x for x in session.query(prods) if len(x[1]) > 1][:10] """ Out[48]: [(2428, [1633667, 1633665, 1633666]), (2462, [1162046, 1162043, 2543783, 1162045]), (2573, [1633697, 1633696]), (2598, [2546824, 922288, 922289]), (2645, [2544843, 338411]), (2660, [1633713, 1633714, 1633712, 1633711]), (2686, [2547480, 466995, 466996]), (2748, [2546706, 2879]), (2785, [467074, 467073, 2545804]), (2806, [2545326, 686295, 686298, 686297])] """ # Ok now try to query to get the first category of each array: [x for x in session.query(prods.c.cats[0].label('first_cat'))] """ (None), (None), (None), (None), (None), (None), (None), (None), (None), (None), (None), """ 

Answer 1

You are probably doing everything right, but first get empty arrays. In the previous query, you used in-python filtering ( len(x[1]) > 1 ). You can print a Query expression before executing it to make sure.

You should probably add a having to the base query:

 cats_agg = func.array_agg(ProductCategory.id, type_=ARRAY(Integer)).label('cats') prods = ( session.query( Product.id.label('id'), cats_agg, .outerjoin( ProductCategory, ProductCategory.product_id == Product.id) .group_by(Product.id) .having(func.array_length(cats_agg, 1) > 1) .subquery() ) 

Then you won't need python filtering.