SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE events (`id` TEXT PRIMARY KEY, `timeslots` TEXT);
✓
CREATE TABLE timeslots (`id` INTEGER, `start` TEXT, `end` TEXT);
✓
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;
✓
INSERT INTO events (`id`, `timeslots`) VALUES
  ('1', '[{"start":"2023-01-01","end":"2023-01-02"},{"start":"2023-02-02","end":"2023-02-04"}]');
✓
SELECT * FROM events;


id
timeslots


1
[{"start":"2023-01-01","end":"2023-01-02"},{"start":"2023-02-02","end":"2023-02-04"}]


SELECT * FROM timeslots;


id
start
end


1
2023-01-01
2023-01-02

1
2023-02-02
2023-02-04