H2 JSON data types with jOOQ DSL

[52 Cards]

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.

http://www.h2database.com/html/grammar.html#json

-- 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!

[Rob Sargent]

have you tried not bothering to add it at all?  Seems the H2 dialect from jOOQ would add that for you?

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/0188b383-c0f6-45df-9cee-9a5a58205dea%40googlegroups.com.

[52 Cards]

If I don't decorate it at all, like  values(myObj.id, myObj.username, myObj.jsonmeta)   then `values` fails to compile because it expects JSON data type.

I can compile using this syntax: JSON.valueOf(JSONObject.toJSONString(myObj.jsonmeta))

(alternatively, using kotlinx.serialization): JSON.valueOf(Json.stringify(MyObj.serializer(), this))

as JOOQ at least recognizes this as a JSON object. However it actually just inserts a string into the database, not native JSON.

The H2 dialect doesn't support JSON currently, which is why I'm trying to manually build the precise SQL statement.  https://www.jooq.org/doc/3.13/manual/sql-building/column-expressions/json-functions/json-object-function/ shows which dialects are supported.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Lukas Eder]

Thanks for your comments. In jOOQ 3.13, you will need to implement a data type binding if you wish to use H2's native JSON support:

https://www.jooq.org/doc/3.13/manual/sql-building/queryparts/custom-bindings/ 

In jOOQ 3.14, we will offer much better JSON support in all dialects, probably including H2. I believe you've already commented on this issue here:

https://github.com/jOOQ/jOOQ/issues/8948  

Thanks,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/2a16fa85-1c34-469d-a6dd-4621c74f4d88%40googlegroups.com.