CODEWITHSUNDEEP
mysqljsonjooq
Ruchira Nawarathna
Ruchira Nawarathna

Reputation: 1507

Querying a json field in mysql using JOOQ

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.

Upvotes: 1

Views: 3159

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220952

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

Upvotes: 1

Related Questions