How to retrieve data from database using jOOQ based on jsontype properties

user_16739133 0
Viewed 192

we have a Employee table with json type address column like mentioned below

Employee Table:

enter image description here

How to retrieve the Employee details based on city name which is in address column using jOOQ?

jooq sql java
1 Answers

You can use the built-in JSON_VALUE() support of jOOQ:

ctx.selectFrom(EMPLOYEE)
   .where(cast(jsonValue(EMPLOYEE.ADDRESS, "$.city"), VARCHAR).eq("\"Mumbai\""))
   .fetch();

This is assuming you use code generation and have the usual static imports:

import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

If you need support for any other vendor-specific JSON function that jOOQ doesn't support (yet), use plain SQL templating instead.

However, that approach is unlikely to take advantage of indexes, so if you can, I suggest normalising this data.

user_5217990
Related