如何使用Knex.js（MySQL）执行JSON_MERGE_PATCH操作？

较新版本的MySQL（以及SQLite和Knex.js支持的其他数据库）提供了一种部分更新JSON列的实用方法：JSON_MERGE_PATCH（在SQLite中也称为JSON_PATCH）。已知Knex.js本身并未提供该功能，可通过其raw()函数调用，但不知道如何无需从头编写完整SQL查询即可实现。

如何在同一条.update()语句中，使用Knex.js执行JSON_MERGE_PATCH操作的同时更新其他非JSON列？

你可以直接在Knex的.update()方法里混合常规列更新和raw()调用的JSON合并操作，不需要编写完整的原生SQL。核心是把JSON_MERGE_PATCH（或SQLite的JSON_PATCH）通过raw()嵌入到更新对象中，同时指定其他非JSON列的更新值。

MySQL环境示例 #

假设存在users表，preferences为JSON类型列，现在要更新用户的name（普通列），同时部分更新preferences里的notifications字段：

await knex('users')
  .where('id', 123)
  .update({
    // 更新普通文本列
    name: '新用户名',
    // 调用JSON_MERGE_PATCH部分更新JSON列
    preferences: knex.raw('JSON_MERGE_PATCH(preferences, ?)', [JSON.stringify({
      notifications: { email: true, sms: false }
    })])
  });

JSON_MERGE_PATCH会保留原JSON列中未修改的字段，仅替换传入的目标内容。

SQLite环境示例 #

SQLite对应的函数是JSON_PATCH，用法逻辑一致：

await knex('users')
  .where('id', 123)
  .update({
    name: '新用户名',
    preferences: knex.raw('JSON_PATCH(preferences, ?)', [JSON.stringify({
      notifications: { email: true, sms: false }
    })])
  });

多数据库兼容方案（可选） #

如果项目需要兼容多种数据库，可以通过Knex的客户端配置判断当前数据库类型，动态选择对应函数：

const updateJsonColumn = (knex, columnName, patchData) => {
  const clientType = knex.client.config.client;
  const jsonFunction = clientType === 'sqlite3' ? 'JSON_PATCH' : 'JSON_MERGE_PATCH';
  return knex.raw(`${jsonFunction}(?, ?)`, [columnName, JSON.stringify(patchData)]);
};

// 使用示例
await knex('users')
  .where('id', 123)
  .update({
    name: '新用户名',
    preferences: updateJsonColumn(knex, 'preferences', {
      notifications: { email: true, sms: false }
    })
  });

这种方式既保留了Knex.js链式查询的简洁性，又能在同一条更新语句中同时处理普通列和JSON列的部分更新，无需编写完整原生SQL。

内容的提问来源于stack exchange，提问作者kgaspard