I'm trying to read/write to an H2 table that has a column with JSON data type.
H2 requires JSON strings to be predicated with `JSON` in order to be saved correctly as a JSON object and not a simple string.
-- example SQL
insert into testtable(id, username, metadata)
values(
25,
'joe',
JSON '{"test":"123"}'
-- '{"test":"123"}' WITHOUT the JSON directive just saves an escaped string: "{\"test\":\"123\"}"
)Alternatively, one can use this syntax:
values(25, 'joe', '{"test":"123"}' FORMAT JSON)How can I build this INSERT with JOOQ? I am trying
jooqDSL.insertInto(Tables.TESTTABLE).columns(
Tables.TESTTABLE.ID, Tables.TESTTABLE.USERNAME, Tables.TESTTABLE.METADATA
).values(
myObj.id,
myObj.username,
field("JSON '$myObj.jsonmeta'", JSON::class.java)
).execute()This doesn't compile, the error is with `
values` :
None of the following functions can be called with the arguments supplied.The main issue is: how do I add that `
JSON` (or `
FORMAT JSON`) keyword? Thank you very much!