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;

Lukas Eder · Accepted Answer · 2021-07-06 11:09:58Z

33

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

edited Jul 6, 2021 at 11:09

answered Aug 1, 2014 at 9:19

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Josh Padnick Over a year ago

Just a comment on this. I understand how to use Plain SQL to manually handle the Postgres JSON data type, but it would be nice if there were a way to get the UpdatableRecord to support json, even if in a slightly hackish way. We use create.newRecord( MY_TABLE, MyPojo ) all the time and I haven't found an elegant way to handle INSERT or UPDATE for json columns.

Lukas Eder Over a year ago

@JoshPadnick: What kind of INSERT or UPDATE would you want to be doing? Just plain data, or some JSON function calls? Best ask a new question, though. It'll be easier to answer than with comments...

Brunaldo Over a year ago

is this answer still a viable solution now?

Lukas Eder Over a year ago

@Brunaldo: Thanks for the ping. Slightly outdated, but in this particular case, yes, the -> operator and the likes are not yet supported. See the workarounds offered in my answer.

Collectives™ on Stack Overflow

Jooq Postgres JSON query

1 Answer 1

Or, write your own mini API

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Or, write your own mini API

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related