Querying a json field in mysql using JOOQ

Question

I have a transaction table and it has a json type field called "request".

| Field               | Type         | Null | Key | Default |
| id                  | bigint       | NO   | PRI | NULL    |
| request             | json         | NO   |     | NULL    |
| response            | json         | YES  |     | NULL    |

request has two attributes currencyCode and amount.

{"amount":100000,"currencyCode":"PHP"}

I can use following mysql query to fetch these values

select json_extract(request, "$.amount") as amount, json_extract(request, "$.currencyCode") as currency from transaction;

| amount | currency |
+--------+----------+
| 100000 | PHP      |
| 100000 | PHP      |
| 100000 | PHP      |

I want to get these values using a jooq query something like this.

DSL.select(<Tables.TRANSACTION.REQUEST.amount>, <Tables.TRANSACTION.REQUEST.currencyCode>)
.from(Tables.TRANSACTION)
.fetch()

I really appreciate if someone can help me with this.

Abhishek Bansal · Accepted Answer · 2021-11-23 13:38:34Z

1

Using jOOQ 3.14's `JSON_VALUE` support

Starting with jOOQ 3.14, you will be able to use the new built-in standard JSON operator support, e.g. JSON_VALUE(). As per the docs:

This example using jOOQ:
jsonValue(val(JSON.json("[1,2]")), "$[*]")
Translates to the following dialect specific expressions:
...
-- MYSQL
json_extract('[1,2]', '$[*]')

Using plain SQL templating in jOOQ 3.13 and earlier

Whenever jOOQ doesn't support vendor specific functionality out of the box, you can resort to using plain SQL templating. Just write:

public static Field<String> jsonExtract(Field<?> field, String jsonPath) {
  return DSL.field("json_extract({0}, {1})", String.class, field, DSL.inline(jsonPath));
}

edited Nov 23, 2021 at 13:38

Abhishek Bansal

629 bronze badges

answered Aug 13, 2020 at 7:52

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

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

6 Comments

dougalg Over a year ago

This question was about how to select the data from a field, not from a val. Can you elaborate on that?

Lukas Eder Over a year ago

@dougalg: val(T) returns Field<T>, so why would it matter?

dougalg Over a year ago

🤦 Woops. Thanks for clarifying.

m-kay Over a year ago

I have tried to use DSL.field("json_unquote(json_extract({0}, {1}))", String.class, field, DSL.inline(jsonPath)); with r2dbc but jOOQ seems to have problems extracting the data as string. The fetched result is something like java.nio.HeapByteBuffer[pos=0 lim=21 cap=21]

m-kay Over a year ago

It is only working when I add an explicit cast: DSL.field("json_unquote(json_extract({0}, {1}))", field, DSL.inline(jsonPath)).cast(String.class);

|

Collectives™ on Stack Overflow

Querying a json field in mysql using JOOQ

1 Answer 1

Using jOOQ 3.14's `JSON_VALUE` support

Using plain SQL templating in jOOQ 3.13 and earlier

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using jOOQ 3.14's JSON_VALUE support

Using plain SQL templating in jOOQ 3.13 and earlier

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related

Using jOOQ 3.14's `JSON_VALUE` support