Type inference for static JSON aggregates

[AlexHorstkoetter]

We often use queries with a static JSON aggregate, for example

SELECT json_agg(json_build_object(
    'firstname': firstname,
    'age': age
)) as family
FROM persons
GROUP BY lastname

The problem is that in this case family has type JsonArray, although they could in theory have the much more concrete and useful type {firstname: string, age: number}[] We have to manually cast the JSON types which is prone to bugs. Is something like this technically feasible? If not, maybe some way to annotate the return type would be nice

[adelsz]

JSON aggregates are quite powerful, but unfortunately they are untyped as is. I do like the idea of introducing a special json keyword that would run type interference on all fields of the json object at compile-time and run the json aggregation at runtime.

[benlongo]

This would allow circumventing the issues that arise when dealing with composite types or unmapped types like ranges. Given the target language is JavaScript, I think this workaround is natural.