[CodesInChaos]

I think it's crazy that standard SQL has no clean way of handling nested data. It's might not fit elegantly into the relational model, but it's still a common business problem that should be addressed. At the bare minimum something like `array_agg` should be standardized.

Alternative query languages like EdgeQL show what first class support for nested data (and navigations) could look like, while the data model is still relational.

[gavinray]

MULTISET is the SQL native way of doing this but it's only implemented in Oracle, and most people have never heard of it

jOOQ emulates this feature for arbitrary databases and has the best explanatory article on the web about MULTISET imo

https://www.jooq.org/doc/latest/manual/sql-building/column-e...

[lukaseder]

Informix also supports MULTISET natively. Many others support ARRAY, which is equivalent for all practical purposes. jOOQ popularised MULTISET over ARRAY because the existing ARRAY support was less user friendly, mapping results to actual Java array types.

[homebrewer]

Is this not what you want? Seems like it's part of the SQL standard.

https://www.postgresql.org/docs/19/ddl-property-graphs.html

[SkiFire13]

That only seems to change how the query is performed, not which kind of data can be returned.

For example how would you use it to return a list of events and, for each event, the list of attendees in that event?

[efromvt]

I guess technically it is in the SQL standard, but optional, as S098? I agree that SQL is sorely lacking here and I'm hoping that the OLAP side innovation (presto, bigquery, snowflake, duckdb all seem to do better) help push it forward.

[lukaseder]

ISO/IEC 9075-2:2023(E) 10.9 <aggregate function>:

<array aggregate function> ::= ARRAY_AGG <left paren> <value expression> [ ORDER BY <sort specification list> ] <right paren>

I hope this helps