Mapping only one row with JOOQ MULTISET

Question

how to use MULTISET to map only one row rather than a list of rows.

Classes:

class User {
    private Integer id;
    private String name;
    private Integer password;
    private Profile profile;
}

class Profile {
    private Integer id;
    private String name;
}

Example Output:

{
    "id": 1,
    "name": "user1",
    "password": "password",
    "profile": {
        "id": 1,
        "name": "admin"
    }
}

Answer 1

You can use row(). Here's an example from the documentation:

List<Author> authors =
create.select(
         AUTHOR.ID,
         row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new)
       )
      .from(AUTHOR)
      .fetch(Records.mapping(Author::new));

Source: https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/nested-records/#attaching-recordmappers-to-nested-records

Answer 2

While you can use the various nested collections and nested records features from jOOQ for type safe mapping, since your target data format is JSON, it's worth asking yourself if you really need the intermediate format, or if you can generate the JSON directly from within SQL.

E.g.

ctx.select(jsonObject(
       jsonEntry(USER.ID),
       jsonEntry(USER.NAME),
       jsonEntry(USER.PASSWORD),
       key("profile").value(jsonObject(
           USER.profile().ID,
           USER.profile().NAME
       ))))
   .from(USER)
   .fetch();