SQLAlchemy、PostgreSQL和array_agg：如何从array_agg中选择项？

Question

我想在一个子查询中使用 array_agg，然后在我的主查询中通过数组的索引来使用聚合后的数据。但是，尝试了很多不同的方法后，我真的不知道该怎么做；有人能解释一下为什么在下面的例子中，我得到了一系列的 None 值，而不是数组中的第一个类别吗？

我明白，下面这个简化的例子可以不通过 SELECT array[i] 来完成，但它能帮助解释问题的本质：

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's 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

你可能做的都没错，只是一开始得到了空数组。在你之前的查询中，你使用了Python内部的过滤方式（len(x[1]) > 1）。你可以在执行查询之前打印出Query表达式，看看是不是这样。

你可能还需要在你的基本查询中添加一个having子句：

from sqlalchemy import Integer
from sqlalchemy.dialects.postgresql import ARRAY

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

这样你就不需要在Python中再进行过滤了。