Support for FILTER Clauses

[karlhorky]

Is your feature request related to a problem? Please describe.

PostgreSQL FILTER clauses don't currently change the type of the resulting rows

Eg. animalFoods.id can also be null here, even though the FILTER clause makes this impossible:

const [animal] = await sql<{ animalFoods: { id: number | null }[]; }[]>`
  SELECT
    json_agg(foods.*) FILTER (WHERE foods.id IS NOT NULL) AS animal_foods
  FROM
    animals
    LEFT JOIN animal_foods ON animals.id = animal_foods.animal_id
    LEFT JOIN foods ON foods.id = animal_foods.food_id
  WHERE
    animals.id = ${id}
`;

Describe the solution you'd like

FILTER clauses should be respected and the type should be altered / narrowed based on the logic in them

Eg. animalFoods.id is only number here:

const [animal] = await sql<{ animalFoods: { id: number }[]; }[]>`
  SELECT
    json_agg(foods.*) FILTER (WHERE foods.id IS NOT NULL) AS animal_foods
  FROM
    animals
    LEFT JOIN animal_foods ON animals.id = animal_foods.animal_id
    LEFT JOIN foods ON foods.id = animal_foods.food_id
  WHERE
    animals.id = ${id}
`;

Describe alternatives you've considered

Other constructs for filtering

Additional context

Original issue with more context:

• JSON type inference #190 (comment)