Jooq Postgres JSON query

Many standard SQL/JSON operators like JSON_ARRAY(), JSON_OBJECT(), JSON_ARRAYAGG() and a few more are supported starting from jOOQ 3.14.

Currently (as of jOOQ 3.15), support for these vendor specific JSON operators is still not implemented: https://github.com/jOOQ/jOOQ/issues/10018

However, you can always resort to using plain SQL. Your query can be expressed as such with jOOQ:

DSL.using(configuration)   .select(BOOKS.ID, field("{0}->'author'->>'first_name'",                        String.class, BOOKS.DATA                     ).as("author_first_name"))   .from(BOOKS)   .fetch();

For details, see the DSL.field() methods javadocs.

Or, write your own mini API

If you're using a lot of these JSON path notations, you might be able to factor out a mini API as such:

public static Field<Object> jsonObject(Field<?> field, String name) {    return DSL.field("{0}->{1}", Object.class, field, DSL.inline(name));}public static Field<String> jsonText(Field<?> field, String name) {    return DSL.field("{0}->>{1}", String.class, field, DSL.inline(name));}

The above could then be used as such:

DSL.using(configuration)   .select(BOOKS.ID, jsonText(jsonObject(BOOKS.DATA, "author"), "first_name")                     .as("author_first_name"))   .from(BOOKS)   .fetch();