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

Question

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?

score 0 · Accepted Answer · answered Dec 16 '22 at 12:23

0

Found this workaround:

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

answered Dec 16 '22 at 12:23

kgaspard

302
2
10

how would you adapt to JSON_ARRAY_APPEND? to add to a json array instead of object – Jabran Saeed Mar 31 '23 at 00:18

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

1 Answers1