mysqlknex.jsjson-patch

How to do a JSON_MERGE_PATCH with Knex.js (MySQL)?

Newer MySQL versions (as well as SQLite and other databases supported by Knex.js) offer a great way to partially update JSON columns: JSON_MERGE_PATCH (aka JSON_PATCH in SQLite). As far as I can tell, Knex.js doesn't offer this functionality. One could use it with the raw() function in Knex.js, but I don't see how we could use it without having to write the whole SQL query from scratch.

How would you use Knex.js to perform a JSON_MERGE_PATCH in a same .update() statement that updates other non-JSON columns as well?

Solution

  • Found this workaround:

    knex(table)
  .where('id',id)
  .update({
    textColumn: textColumnUpdatedString,
    // ... other columns
    jsonColumn: knex.raw('JSON_MERGE_PATCH(??,?)',['jsonColumn', JSON.stringify(jsonUpdateObject)]) }
  })