Jooq Postgres JSON Request

Question

What kind of support exists for requests to postgres js objects with JOOQ?

For example,

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

Answer 1

Currently (as of jOOQ 3.4) there is no built-in support for these PostgreSQL-specific JSON statements in jOOQ. There is query # 2788 to support the PostgreSQL json data type.

However, you can always use plain SQL . Your request can be expressed as such using jOOQ:

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

See DSL.field() javadocs for more details.

Or write your own mini API

If you use a lot of these JSON path descriptions, you can split the 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 can be used as such:

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