pypika
pypika copied to clipboard
Subquery isn't surround in params of aggregate function
Recently, I use aggregate functions and I passed a subquery to an aggregate function and it should be surround because Postgres does not allow that
Example:
from pypika import Query, Tables
from pypika.terms import AggregateFunction
parent, child = Tables('parent', 'child')
childrent_subquery = Query.from_(child).select(
AggregateFunction(
'json_agg',
AggregateFunction(
'json_build_object',
'name', child.name,
'age', child.age,
))
).where(child.parent_id == parent.id)
main_query = Query.from_(parent).select(
AggregateFunction(
'json_agg',
AggregateFunction(
'json_build_object',
'name', parent.name,
'childrent', childrent_subquery,
)
)
)
print(main_query.get_sql())
Actual
SELECT
json_agg(json_build_object('name', "name", 'childrent',
SELECT
json_agg(json_build_object('name', "child"."name", 'age', "child"."age"))
FROM "child"
WHERE
"child"."parent_id" = "parent"."id"))
FROM
"parent"
Expect
SELECT
json_agg(json_build_object('name', "name", 'childrent',
(SELECT
json_agg(json_build_object('name', "child"."name", 'age', "child"."age"))
FROM "child"
WHERE
"child"."parent_id" = "parent"."id")))
FROM
"parent"