我正在使用此 sqlalchemy 查询来获取所需格式的结果:
query = select(
[
Product.name,
Product.description,
Product.turn_around_time,
Product.id.label("product_id"),
func.array_agg(
func.json_build_object("label", Field.label ,"type", Field.field_type)
).label("product_fields")
],
).select_from(
join(Product, ProductField).join(Field)
).where(
and_(
Product.id == product_id,
Product.organization_id == user.organization_id
)
).group_by(Product.id)
该查询是该 postgres 查询的一对一映射,其工作原理如下:
select product.name,
product.description,
product.turn_around_time,
array_agg(json_build_object('lable', field.label,'type', field.field_type))
from product
inner join product_field on product.id = product_field.product_id
inner join field on field.id = product_field.field_id
where product.id = 'f2778d60-dc63-407f-9a4e-e23595ef6dd1'
group by product.id;
这是我得到的错误:
statement = await self._protocol.prepare(stmt_name, query, timeout)
File "asyncpg/protocol/protocol.pyx", line 163, in prepare
asyncpg.exceptions.IndeterminateDatatypeError: could not determine data type of parameter $2
但如果我改变这一行: func.json_build_object("label", Field.label ,"type", Field.field_type)
to
func.json_build_object( Field.label, Field.field_type)
它有效,但结果是不同的格式。