How to implement JSON_SET() in jOOQ for MySQL 8

Question

I have a table with a JSON col

--------------------------------------
Col1 | Col2 | JSON_col | Key1 | Key2 |
--------------------------------------

The structure of JSON_Col is Map, so in DB it is {0: {data here}}

And I have a requirement to implement something like this:

update <table> set JSON_Col = JSON_SET(JSON_Col, $.key1, data1, $.key2, data2) 
where <condition>

How can I write such thing in jOOQ.

Answer 1

In future versions of jOOQ, we'll support additional built in JSON functionality, and JSON_SET might be part of it (see #10018).

As of jOOQ 3.12, you can write your own jOOQ extension, as always when some vendor specific functionality is not supported, using plain SQL templating, e.g.

public static Field<JSON> jsonSet(Field<JSON> field, Map<String, String> map) {
    StringBuilder sb = new StringBuilder();
    String separator = "";
    int i = 1;
    for (var e : map.entrySet()) {
        sb.append(separator);
        sb.append("$.").append(e.getKey()).append(", {").append(i++).append("}");

        separator = ", ";
    }

    List<QueryPart> queryParts = new ArrayList<>();
    queryParts.add(field);
    for (var v : map.values())
        queryParts.add(DSL.val(v));

    return DSL.field("json_set({0}, " + sb.toString() + ")",
        field.getDataType(),
        queryParts.toArray(new QueryPart[0]));
}

And then, use it like this:

ctx.update(TABLE)
   .set(TABLE.JSON_COL, jsonSet(TABLE.JSON_COL, Map.of("key1", "data1", "key2", "data2")))
   .where(...)
   .execute();