Search code examples
jsonpostgresqljooq

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

Having read this post suggesting that it's sometimes a good trade-off to use JSON operators to return JSON directly from the database; I'm exploring this idea using PostgreSQL and JOOQ.

I'm able to write SQL queries returning a JSON array of JSON objects rather than rows using the following pattern:

select jsonb_pretty(array_to_json(array_agg(row_to_json(r)))::jsonb)
from (
  select [subquery]
) as r;

However, I failed to translate this pattern in JOOQ.

Any help regarding how to translate a collection of rows (fields being of "usual" SQL type or already mapped as json(b)) using JOOQ would be appreciated.

Solution

  • 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:

    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.