SQL:2003 introduced filtered aggregations, e.g. SELECT COUNT(*) FILTER(WHERE foo > 0). They're currently supported in Postgres and SQLite.
If I want to use this syntax in Jet, I have to resort to Raw, which is inconvenient. I'd like to be able to build the FILTER(WHERE ...) clause in the same way I build any other WHERE clause.
To be fair, the ANSI SQL form of the FILTER clause is in many cases essentially syntactic sugar, and it can usually be emulated with a CASE statement, but this is not always true. Taking the CASE approach typically relies on aggregate functions ignoring nulls, but this does not work with functions such as Postgres' json_arrayagg, where nulls may or may not be a desired part of the output. Postgres also extends standard SQL by allowing subqueries like EXISTS() in the FILTER clause, and that can't be emulated with a CASE statement either.
Possible solution
Aggregate functions in dialects that support this syntax could return an extended Expression object that would allow adding a FILTER clause, similar to how the window OVER clause is implemented. Rough sketch:
Problem
SQL:2003 introduced filtered aggregations, e.g.
SELECT COUNT(*) FILTER(WHERE foo > 0)
. They're currently supported in Postgres and SQLite.If I want to use this syntax in Jet, I have to resort to
Raw
, which is inconvenient. I'd like to be able to build theFILTER(WHERE ...)
clause in the same way I build any otherWHERE
clause.To be fair, the ANSI SQL form of the
FILTER
clause is in many cases essentially syntactic sugar, and it can usually be emulated with aCASE
statement, but this is not always true. Taking theCASE
approach typically relies on aggregate functions ignoring nulls, but this does not work with functions such as Postgres'json_arrayagg
, where nulls may or may not be a desired part of the output. Postgres also extends standard SQL by allowing subqueries likeEXISTS()
in theFILTER
clause, and that can't be emulated with aCASE
statement either.Possible solution
Aggregate functions in dialects that support this syntax could return an extended
Expression
object that would allow adding aFILTER
clause, similar to how the windowOVER
clause is implemented. Rough sketch: