Jooq Postgres JSON query

Question

What support is there for querying into postgres json objects with JOOQ?

For example,

SELECT id, data->'author'->>'first_name' as author_first_name FROM books;

Answer 1

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();