MULTISET_AGG

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME,
  MULTISET_AGG(
    BOOK.ID,
    BOOK.TITLE,
    LANGUAGE.CD
  )
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
JOIN LANGUAGE ON BOOK.LANGUAGE_ID = LANGUAGE.ID
GROUP BY
  AUTHOR.ID,
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME
ORDER BY AUTHOR.ID

create.select(
          AUTHOR.FIRST_NAME,
          AUTHOR.LAST_NAME,
          multisetAgg(
              BOOK.ID,
              BOOK.TITLE,
              BOOK.language().CD
          ).as("books"))
      .from(AUTHOR)
      .join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
      .groupBy(
          AUTHOR.ID,
          AUTHOR.FIRST_NAME,
          AUTHOR.LAST_NAME)
      .orderBy(AUTHOR.ID)
      .fetch()

+----------+---------+---------------------------------------+
|first_name|last_name|books                                  |
+----------+---------+---------------------------------------+
|George    |Orwell   |[(2, Animal Farm, en), (1, 1984, en)]  |
|Paulo     |Coelho   |[(4, Brida, de), (3, O Alquimista, pt)]|
+----------+---------+---------------------------------------+

multisetAgg(BOOK.ID, BOOK.TITLE)

-- AURORA_POSTGRES, COCKROACHDB, POSTGRES, YUGABYTEDB
jsonb_agg(jsonb_build_array(BOOK.ID, BOOK.TITLE))

-- DB2
xmlelement(
  NAME result,
  xmlagg(xmlelement(
    NAME record,
    xmlelement(NAME v0, BOOK.ID),
    xmlelement(
      NAME v1,
      xmlattributes(
        CASE
          WHEN BOOK.TITLE IS NULL THEN 'true'
        END AS xsi:nil
      ),
      BOOK.TITLE
    )
  ))
)

-- H2
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL))

-- MARIADB, MYSQL
json_merge_preserve(
  '[]',
  concat(
    '[',
    group_concat(json_array(BOOK.ID, BOOK.TITLE) SEPARATOR ','),
    ']'
  )
)

-- ORACLE
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL RETURNING clob) FORMAT JSON RETURNING clob)

-- SNOWFLAKE
array_agg(array_construct(coalesce(
  to_variant(BOOK.ID),
  parse_json('null')
), coalesce(
  to_variant(BOOK.TITLE),
  parse_json('null')
)))

-- SQLITE
json_group_array(json_array(BOOK.ID, BOOK.TITLE))

-- TERADATA
xmlelement(
  NAME "result",
  xmlagg(xmlelement(
    NAME record,
    xmlelement(NAME v0, BOOK.ID),
    xmlelement(
      NAME v1,
      xmlattributes(
        CASE
          WHEN BOOK.TITLE IS NULL THEN 'true'
        END AS nil
      ),
      BOOK.TITLE
    )
  ))
)

-- TRINO
cast(array_agg(CAST(ARRAY[
  CAST(BOOK.ID AS json),
  CAST(BOOK.TITLE AS json)
] AS json)) AS json)

-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MEMSQL, REDSHIFT, 
-- SQLDATAWAREHOUSE, SQLSERVER, SYBASE, VERTICA
/* UNSUPPORTED */