Insert data into JSON column in postgres using JOOQ

Question

I have a postgres database to which I read/write using JOOQ. One of my DB tables has a column of type JSON. When I try to insert data into this column using the query below, I get the error

Exception in thread "main" org.jooq.exception.DataAccessException: SQL [update "public"."asset_state" set "sites_as_json" = ?]; ERROR: column "sites_as_json" is of type json but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.

Below is the code for inserting data into the column

SiteObj s1 = new SiteObj();
s1.setId("1");
s1.setName("Site1");
s1.setGeofenceType("Customer Site");

SiteObj s2 = new SiteObj();
s2.setId("2");
s2.setName("Site2");
s2.setGeofenceType("Customer Site");

List<SiteObj> sitesList = Arrays.asList(s1, s2);
int result = this.dsl.update(as).set(as.SITES_AS_JSON, LambdaUtil.convertJsonToStr(sitesList)).execute();

The call LambdaUtil.convertJsonToStr(sitesList) outputs a string that looks like this...

[{"id":"1","name":"Site1","geofenceType":"Customer Site"},{"id":"2","name":"Site2","geofenceType":"Customer Site"}]

What do I need to do to be able to insert into the JSON column?

Answer 1

Current jOOQ versions

jOOQ natively supports JSON and JSONB data types. You shouldn't need to have to do anything custom.

Historic answer

For jOOQ to correctly bind your JSON string to the JDBC driver, you will need to implement a data type binding as documented here:

https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

The important bit is the fact that your generated SQL needs to produce an explicit type cast, for example:

@Override
public void sql(BindingSQLContext<JsonElement> ctx) throws SQLException {
    // Depending on how you generate your SQL, you may need to explicitly distinguish
    // between jOOQ generating bind variables or inlined literals.
    if (ctx.render().paramType() == ParamType.INLINED)
        ctx.render().visit(DSL.inline(ctx.convert(converter()).value())).sql("::json");
    else
        ctx.render().sql("?::json");
}