3 ms·

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 b

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.

homebrewer 3mo ago
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 https://www.postgresql.org/docs/19/ddl-property-graphs.html
- SkiFire13 3mo ago
  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?
gavinray 3mo ago
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-expressions/multiset-value-constructor/ https://www.jooq.org/doc/latest/manual/sql-building/column-e...
- lukaseder 3mo ago
  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.
efromvt 3mo ago
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 3mo ago
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

homebrewer 3mo ago

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 https://www.postgresql.org/docs/19/ddl-property-graphs.html

SkiFire13 3mo ago
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?

gavinray 3mo ago

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-expressions/multiset-value-constructor/ https://www.jooq.org/doc/latest/manual/sql-building/column-e...

lukaseder 3mo ago
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.

efromvt 3mo ago

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 3mo ago

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