如何使用jOOQ根据JSON类型字段属性查询数据库数据

现有Employee表结构如下：

其中address为JSON类型字段，内部存储包含城市信息的结构化地址数据，可按以下方案实现按城市检索员工：

通用方案（jOOQ 3.14及以上版本推荐） #

jOOQ 3.14开始内置了跨方言的JSON操作函数，不需要适配不同数据库的语法差异，直接使用jsonValue()方法提取JSON字段值即可：

import static org.jooq.impl.DSL.*;
// 替换为你自己项目生成的jOOQ表类路径
import static com.your.project.generated.Tables.EMPLOYEE;

// 示例：查询address中city等于"上海"的所有员工
List<EmployeeRecord> result = dslContext
    .selectFrom(EMPLOYEE)
    .where(jsonValue(EMPLOYEE.ADDRESS, "$.city").eq("上海"))
    .fetch();

该方法会自动根据你配置的数据库方言生成对应SQL：

• MySQL环境自动生成JSON_EXTRACT(address, '$.city') = '上海'
• PostgreSQL环境自动生成address ->> 'city' = '上海'
• 其他支持JSON的数据库也会自动适配对应语法

低版本jOOQ兼容方案 #

如果你使用的是3.14之前的jOOQ版本，可以直接拼接对应数据库的原生JSON函数：

MySQL环境示例 #

List<EmployeeRecord> result = dslContext
    .selectFrom(EMPLOYEE)
    .where(field("JSON_EXTRACT(address, '$.city')", String.class).eq("上海"))
    .fetch();

PostgreSQL环境示例 #

List<EmployeeRecord> result = dslContext
    .selectFrom(EMPLOYEE)
    .where(field("address ->> 'city'", String.class).eq("上海"))
    .fetch();

注意事项 #

• 如果JSON中城市的嵌套路径更深，比如结构为{"province": {"name":"XX","city":"XX"}}，只需将JSON路径参数修改为$.province.city即可
• 数据量较大时建议为JSON字段的city键创建函数索引，避免全表扫描降低查询性能
• 提取后的JSON值可以像普通字符串字段一样使用like()、in()等运算符，用法和常规字段完全一致

内容的提问来源于stack exchange，提问作者Prashanth Billa