SQLAlchemy,PostgreSQL和array_agg:如何从array_agg中选择项目? [英] SQLAlchemy, PostgreSQL and array_agg: How to select items from array_agg?
问题描述
我想在子查询中使用 array_agg ,然后在我的主查询中利用数组索引使用聚合数据,但是,在尝试了许多不同的方法之后,我真的对应该如何做感到茫然。有人可以解释为什么在下面的示例中我得到一系列None值而不是数组中的第一个类别吗?
I want to use array_agg in a subquery, then make use of the aggregated data by it's array index in my main query, however, after trying many different ways, I'm really at a loss as to how it should be done; could someone explain why in the example below I get a series of None values instead of the first category in the array?
我了解可以完成以下简化示例无需对array [i]进行SELECT,但是它将说明问题的性质:
I understand that the following, simplified example can be done without doing a SELECT on array[i], 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'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),
"""
推荐答案
您可能所做的一切正确,但首先获取空数组。在上一个查询中,您使用了Python内过滤( len(x [1])> 1
)。您可以在执行前先打印 Query
表达式以确认。
You are probably doing everything right, but getting empty arrays first. In your previous query you used in-python filtering (len(x[1]) > 1
). You can print Query
expression before executing it to make sure.
您可能应该添加具有
子句的基本查询:
You should probably add a having
clause to you 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()
)
然后您赢了
这篇关于SQLAlchemy,PostgreSQL和array_agg:如何从array_agg中选择项目?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!