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 On Tue, May 19, 2020 at 7:46 AM 52 Cards <52ca...@gmail.com> wrote: > 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/ > <https://www.jooq.org/doc/3.13/manual/sql-building/column-expressions/json-functions/json-object-function/> > shows which dialects are supported. > > > > On Monday, May 18, 2020 at 8:41:42 PM UTC-7, Rob Sargent wrote: >> >> have you tried not bothering to add it at all? Seems the H2 dialect from >> jOOQ would add that for you? >> >> On 5/18/20 9:05 PM, 52 Cards wrote: >> >> 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! >> >> -- >> 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...@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 >> <https://groups.google.com/d/msgid/jooq-user/0188b383-c0f6-45df-9cee-9a5a58205dea%40googlegroups.com?utm_medium=email&utm_source=footer> >> . >> >> >> -- > 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+unsubscr...@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 > <https://groups.google.com/d/msgid/jooq-user/2a16fa85-1c34-469d-a6dd-4621c74f4d88%40googlegroups.com?utm_medium=email&utm_source=footer> > . > -- 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAB4ELO48TgbrZpdJR5%3DBmJXavExhFQy0fXGdCDMOPkNGaNbM5g%40mail.gmail.com.
