sqlite3でsplit（もっと簡単に）

この記事で説明すること

SQLアンチパターンでいうところのジェイウォークパターンになっているデータを、SQLiteを使って正規化する方法を説明します。

次の記事では再帰クエリを使って実現していますが、SQLite で JSON1 拡張が使える場合はもう少し簡単に実現可能です。

方法

• 関数を駆使してJSON配列に変換する。
  • \ を \\ に、 " を \" に置き換える。
  • 区切り記号 , を "," に置き換える。
  • 周りを [" と "] で括る。
• json_each テーブル値関数¹を使ってJSON配列の値を読み出す。

CREATE TABLE kv (k, v);

INSERT INTO kv VALUES
    ('key1', 'this,is,a,pen'),
    ('key2', 'hello,world'),
    ('key3', 'I,am,a,boy'),
    ('symbols', '",\,'''),
    ('null', NULL);

SELECT * FROM kv;

SELECT kv.k, sv.value AS v
FROM kv
LEFT JOIN json_each(
    '["'
    || replace(replace(replace(
            kv.v,
            '\', '\\'),
            '"', '\"'),
            ',', '","')
    || '"]'
) as sv;

---

1. SQLiteのテーブル値関数 (table-valued function) とは、関数のように使える仮想テーブルのことで、FROM節中でJOINして使うことができます。 The Virtual Table Mechanism Of SQLite 2.1.2. Table-valued functions ↩