Language-Integrated Query with Nested Data Structures and Grouping

Language-integrated query adds to database query the power of highlevel programming languages such as abstraction, compositionality, and nested data structures. Cheney et al. designed a two-level typed language for it and showed that any closed term of suitable type can be normalized to a single SQL query which does not have nested data structures nor nested SELECT clauses. This paper extends their language to cover the GROUP BY clause in SQL to express grouping and aggregate functions. Although the GROUP BY clause is frequently used, it is not covered by existing studies on efficient implementation of language-integrated queries. In fact, it seems impossible to express composition of two aggregate functions by a single aggregate function, therefore, there exists a query with nested GROUP BY clauses which has no equivalent query without nested one. However, since several database engines such as PostgreSQL allow nested queries, we can still ask if it is possible to convert an arbitrary query with grouping and aggregation to a single query in SQL which allows nested queries, but disallows nested data structures such as a table of tables. This paper solves the latter question affirmatively. Our key observation is that the GROUP BY clause in SQL does two different kinds of things: manipulating input/output data and grouping with aggregation, the former can be transformed, but may have complex types, while the latter cannot be transformed, but has simple types. Hence, we decouple the GROUP BY clause and introduce primitives into our language-integrated query to obtain a calculus which can express GROUP BY. We then show our language has the normalization property that every query is converted to a single query which does not have nested data structures. We conduct simple benchmarks which show that queries in our language can be transformed to efficient SQL queries.