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 [email protected] <javascript:>. > 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/2a16fa85-1c34-469d-a6dd-4621c74f4d88%40googlegroups.com.
