sqlalchmey func.json_build_object throwing error, could not determine data type of paramater

user_6518118 0
Viewed 320

I am using this sqlalchemy query to to get a result in desired format:

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)

This query is one to one map of this postgres query which works:

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;

This is the error i get :

    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

But if i change this line : func.json_build_object("label", Field.label ,"type", Field.field_type) to func.json_build_object( Field.label, Field.field_type) it works but the result is in different format.

sqlalchemy python
1 Answers

Not the most elegant way, but this worked for me

func.array_agg(
    func.json_build_object(
        text("'key', table_name.field"),
        text("'key', table_name.field"),
        text("'key', table_name.field")
    )
).label("product_fields"),
user_69274980
Related