Using PostgreSQL row_to_json with jOOQ

Question

Is there anything more idiomatic than

    List<JSON> jsons = context.fetch("select row_to_json(p) j from persons p")
        .stream()
        .map(r -> r.get("j", JSON.class))
        .toList();

to use row_to_json() with jOOQ?

I tried using function("row_to_json", ...) but couldn't figure out how to fill the remaining arguments.

Answer 1

This kind of syntax (e.g. Oracle has JSON_OBJECT(*)) might be supported in the near future out of the box via #10685.

The DSL.function() method requires a Field<?>... list, so it cannot accept a table, currently. But you can use a plain SQL template also for your function usage, without having to make the entire query a template, e.g. like this:

static Field<JSON> rowToJson(Table<?> t) {
    return DSL.field("row_to_json({0})", SQLDataType.JSON, t);
}

And now use it like this:

context.select(rowToJson(PERSONS)).from(PERSONS).fetch();