Jooq Postgres JSON 查询

Question

JOOQ支持在Postgres JSON对象中进行查询吗？

例如：

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

- Joel

Answer 1

从 jOOQ 3.14 开始，支持许多标准的 SQL/JSON 运算符，例如 JSON_ARRAY()、JSON_OBJECT()、JSON_ARRAYAGG() 等。

目前（截至 jOOQ 3.15），仍未实现对这些特定供应商的 JSON 运算符的支持：https://github.com/jOOQ/jOOQ/issues/10018

但是，您可以随时使用普通 SQL。您可以使用 jOOQ 表达如下查询：

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

有关详细信息，请参阅DSL.field()方法的Javadocs文档。

或者，编写您自己的迷你API

如果您正在使用许多这些JSON路径表示法，则可以将其作为迷你API拆分出来，例如：

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

那么上述内容可以这样使用：

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

- Lukas Eder

---

仅对此发表评论。我知道如何使用普通SQL手动处理Postgres JSON数据类型，但如果有一种方法可以以稍微hackish的方式使UpdatableRecord支持json，那将是很好的。我们经常使用create.newRecord（MY_TABLE，MyPojo），但我还没有找到一个优雅的方法来处理json列的INSERT或UPDATE。 - Josh Padnick

@JoshPadnick：你想要进行什么类型的INSERT或UPDATE操作？只是简单的数据，还是一些JSON函数调用？最好提出一个新问题，这样回答起来比在评论中更容易... - Lukas Eder

这个答案现在仍然是可行的解决方案吗？ - Brunaldo

@Brunaldo：感谢提醒。虽然有点过时，但在这种特殊情况下，确实不支持->运算符等。请参考我的回答中提供的解决方法。 - Lukas Eder