How to return an array of JSON objects rather the a collection of rows using JOOQ & PostgreSQL

SQL Server FOR JSON semantics

That's precisely what the SQL Server FOR JSON clause does, which jOOQ supports and which it can emulate for you on other dialects as well:

ctx.select(T.A, T.B)
   .from(T)
   .forJSON().path()
   .fetch();

PostgreSQL native functionality

If you prefer using native functions directly, you will have to do with plain SQL templating for now, as some of these functions aren't yet supported by jOOQ, including:

• JSONB_PRETTY (no plans of supporting it yet)
• ARRAY_TO_JSON (https://github.com/jOOQ/jOOQ/issues/12841)
• ROW_TO_JSON (https://github.com/jOOQ/jOOQ/issues/10685)

It seems quite simple to write a utility that does precisely this:

public static ResultQuery<Record1<JSONB>> json(Select<?> subquery) {
    return ctx
        .select(field(
            "jsonb_pretty(array_to_json(array_agg(row_to_json(r)))::jsonb)", 
            JSONB.class
         ))
        .from(subquery.asTable("r"))
}

And now, you can execute any query in your desired form:

JSONB result = ctx.fetchValue(json(select(T.A, T.B).from(T)));

Converting between PG arrays and JSON arrays

A note on performance. It seems that you're converting between data types a bit often. Specifically, I'd suggest you avoid aggregating a PostgreSQL array and turning that into a JSON array, but to use JSONB_AGG() directly. I haven't tested this, but it seems to me that the extra data structure seems unnecessary.