如何用UPDATE语句修改JSON键值对？SQLite及Node.js实现方法

一、用SQL查询直接实现（SQLite 3.38.0+） #

SQLite 从3.38.0版本开始原生支持JSON操作函数，你可以直接用json_set()函数完成目标更新：

UPDATE your_table_name
SET json_column = json_set(json_column, '$.foo', 'changed_value')
-- 可添加WHERE条件定位特定行，比如 WHERE id = 1;

json_set()的作用是修改JSON对象中指定路径的属性值，$.foo就是定位到foo键的路径。

二、Node.js实现（适用于低版本SQLite） #

如果你的SQLite版本不支持JSON函数，可以用Node.js结合sqlite3库快速完成，以下是最简实现：
首先安装依赖：

npm install sqlite3

回调风格代码 #

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('./your_database.db'); // 替换为你的数据库路径

// 示例：更新id为1的行
db.get('SELECT json_column FROM your_table_name WHERE id = ?', [1], (err, row) => {
  if (err) throw err;
  const jsonData = JSON.parse(row.json_column);
  jsonData.foo = 'changed_value';
  db.run('UPDATE your_table_name SET json_column = ? WHERE id = ?', [JSON.stringify(jsonData), 1], (err) => {
    if (err) throw err;
    console.log('更新完成');
    db.close();
  });
});

Async/Await风格代码（更易读） #

const sqlite3 = require('sqlite3').verbose();
const { promisify } = require('util');
const db = new sqlite3.Database('./your_database.db');
// 转换为Promise风格方法
db.get = promisify(db.get);
db.run = promisify(db.run);

(async () => {
  try {
    const row = await db.get('SELECT json_column FROM your_table_name WHERE id = ?', [1]);
    const jsonData = JSON.parse(row.json_column);
    jsonData.foo = 'changed_value';
    await db.run('UPDATE your_table_name SET json_column = ? WHERE id = ?', [JSON.stringify(jsonData), 1]);
    console.log('更新完成');
  } catch (err) {
    console.error(err);
  } finally {
    db.close();
  }
})();

内容的提问来源于stack exchange，提问作者Александр Третинов