Jooq: Add org.jooq.MultiSet, a QueryPart to model SQL standard MULTISETs (nested tables)

[lukaseder]

The SQL standard specifies a very interesting ORDBMS feature: MULTISET which are essentially nested tables).

An example:

SELECT
  first_name,
  last_name,
  MULTISET(
    SELECT * FROM book WHERE author_id = author.id
  ) books
FROM author

Multiset are a much more intuitive way to join data in a hierarchical manner, rather than joining them via JOIN clauses in the FROM clause. In particular, these nested tables are much more similar to the way object oriented design works, by nesting type hierarchies.

Databases that support MULTISET:

• CUBRID
• Informix
• Oracle

Databases that might be able to emulate MULTISET:

• PostgreSQL (via array_agg(), aggregating ROW() types into arrays)
• SQL Server (via XML nested result set encoding: https://www.reddit.com/r/programming/comments/5tp522/net_renaissance/ddp58ht/)
• Many others via XML or JSON

We need a new Settings.emulateMultiset with the following options:

• [ ] EmulateMultiset.DEFAULT - Use a native implementation if possible, or the "most optimal emulation", otherwise
• [ ] EmulateMultiset.XML
• [ ] EmulateMultiset.JSON

Rejected:

• There is a possibility of MULTISET usage in SELECT clauses being transformed into separate queries that allow for emulating this feature - at least for simple join conditions.

  There are too many open questions in this area, and the SQL/XML or SQL/JSON emulation is too tempting. Let's focus on this, first.

[nakolkin]

Hi Lukas, this feature looks very promising! Wondering if there is ongoing work to actually implement it? In the meantime, could you suggest any best practices to fetch/map object hierarchies with JOOQ? I've read this blog post https://blog.jooq.org/2014/04/11/java-8-friday-no-more-need-for-orms/ but maybe there are some updates in that field. Thanks!

[lukaseder]

No, there hasn't been any progress on this feature - more important features have been prioritised in the recent past.

We usually recommend using a third party mapper, such as simpleflatmapper by @arnaudroger
https://github.com/arnaudroger/SimpleFlatMapper

[lukaseder]

Picking this up again for jOOQ 3.15.

jOOQ 3.14 now supports the necessary underlying infrastructure to emulate this in dialects without native support, i.e. everywhere:

• CUBRID support is deprecated
• Informix might still be able to do it
• Oracle requires named table and object types, which we don't have (we could create them on the fly, but that raises new questions, besides, Oracle has complete standard SQL/XML and SQL/JSON support)

An example of how nested collections can be mapped to POJOs using SQL/JSON and a mapping library like Jackson/Gson has been given in a few Stack Overflow questions, e.g. https://stackoverflow.com/a/64314221/521799, or in this blog post:
https://blog.jooq.org/2020/10/09/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/

For example, in order to map into this data structure:

class Student {
  List<Teacher> teachers;
  List<Book> books;
}

We can write this SQL using SQL/JSON

List<Student> students =
ctx.select(jsonObject(
     jsonEntry("name", STUDENT.NAME),
     jsonEntry("id", STUDENT.ID),
     jsonEntry("teachers", field(
       select(jsonArrayAgg(jsonObject(TEACHER.NAME, TEACHER.ID)))
       .from(TEACHER)
       .join(STUDENT_TEACHER).on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     )),
     jsonEntry("books", field(
       select(jsonArrayAgg(jsonObject(BOOK.NAME, BOOK.ID)))
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ))
   ))
   .from(STUDENT)
   .fetchInto(Student.class);

Using MULTISET, the following should be possible:

List<Student> students =
ctx.select(
     STUDENT.NAME,
     STUDENT.ID,
     multiset(
       select(TEACHER.NAME, TEACHER.ID)
       .from(TEACHER)
       .join(STUDENT_TEACHER).on(TEACHER.ID.eq(STUDENT_TEACHER.TEACHER_ID))
       .where(STUDENT_TEACHER.STUDENT_ID.eq(STUDENT.ID))
     ).as("teachers"),
     multiset(
       select(BOOK.NAME, BOOK.ID)
       .from(BOOK)
       .join(STUDENT_BOOK).on(BOOK.ID.eq(STUDENT_BOOK.BOOK_ID))
       .where(STUDENT_BOOK.STUDENT_ID.eq(STUDENT.ID))
     ).as("books")
   )
   .from(STUDENT)
   .fetchInto(Student.class);

I.e. almost the same, except that:

• the jsonXYZ() boilerplate is removed
• there is now type information for the nested collections available. The resulting type (if not mapping into a pojo) will be:
  java Result<Record4<String, Long, Result<Record2<String, Long>>, Result<Record2<String, Long>>>>

[JayAhn2]

@lukaseder Is the multiselect syntax planned to be released in 3.15?
I love this syntax lol 👍
nice job! 💯

[lukaseder]

@JayAhn2 No promises 😉