如何用SQLite触发器将JSON对象数组转换为新记录？

问题分析 #

原触发器存在两个关键错误：

1. SELECT语句中最后一个字段后多了一个多余的逗号，触发SQL语法错误。
2. 尝试从JSON数组元素中提取id字段，但timeslots数组元素里并没有该属性，应使用插入的events行的NEW.id来关联两张表。

修正后的触发器代码 #

CREATE TRIGGER IF NOT EXISTS insert_timeslots
  AFTER INSERT ON events
  BEGIN
    INSERT INTO timeslots (id, start, end)
    SELECT 
      NEW.id,
      json_extract(t.value, '$.start'),
      json_extract(t.value, '$.end')
    FROM json_each(NEW.timeslots) AS t;
END;

额外说明 #

如果你的timeslots表实际需要将JSON中的start存入表的end字段、JSON中的end存入表的start字段（匹配你给出的表示例），则调整字段映射如下：

CREATE TRIGGER IF NOT EXISTS insert_timeslots
  AFTER INSERT ON events
  BEGIN
    INSERT INTO timeslots (id, end, start)
    SELECT 
      NEW.id,
      json_extract(t.value, '$.start'),
      json_extract(t.value, '$.end')
    FROM json_each(NEW.timeslots) AS t;
END;

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