SQLite3 JSONの配列を複数レコードに展開する(json_each())

概要

SQLite は JSON をサポートしており（ver 3.38.0 以降）、JSON Path や json_extract関数などを使用して 値を取得できる。
ただし、JSON 内の配列を取得した場合でも、配列の各要素が複数行のレコードに展開されるわけではない。 例えば、JSON Path を使って配列を取得すると、配列全体が JSON 文字列として1つの値として取得される。

配列の中身を複数行のレコードとして取得するのであれば、 json_eachや json_treeを使う必要がある。

この記事には json_eachを使って JSON 配列を複数行に展開する方法を記載する。

構文

json_each(json)
json_each(json, json_path)
                

引数の json は JSON 文字列を指定する。 json_path は省略可能で、JSON Path を指定する。 省略した場合は、JSON のルートから展開される。
JSON Path については以下の記事を参照。

• SQLite3で使えるJSON Path

この関数は SELECTではなくFROMの後に続けて使う。 json_each()は単一の値ではなくテーブル構造を返すようで、 このような関数を「テーブル値関数」というらしい。

使用例

環境

• Windows 11
• SQLite Command-Line Shell ver 3.54.1

JSONの配列の要素を行（レコード）として取得する

JSON配列のテキストを展開して行として取得してみる。

sample テーブル

id	json
1	["tomato", "potato", "pumpkin"]
2	["onion", "carrot"]

単純に SELECT した場合

SQLite-3.54 memory-> SELECT * FROM sample;
╭────┬─────────────────────────────────╮
│ id │              json               │
╞════╪═════════════════════════════════╡
│  1 │ ["tomato", "potato", "pumpkin"] │
│  2 │ ["onion", "carrot"]             │
╰────┴─────────────────────────────────╯
                        

jsonはそのまま文字列として取得される。

json_each() を使う場合

SQLite-3.54 memory-> SELECT sample.id, json_each.value FROM sample, json_each(sample.json);
╭────┬─────────╮
│ id │  value  │
╞════╪═════════╡
│  1 │ tomato  │
│  1 │ potato  │
│  1 │ pumpkin │
│  2 │ onion   │
│  2 │ carrot  │
╰────┴─────────╯
                        

sample.json の配列の各要素が複数行のレコードとして取得される。

SQL を見やすくするとこんな感じ。

SELECT
    sample.id
    , json_each.value
FROM
    sample
    , json_each(sample.json);
                        

ポイントとしては以下の通り。

• FROMの後にjson_each()を記載する
• json_each()の引数として指定するのとは別に JSON を格納しているテーブルをFROMの後に記載する
• json_each()から返されるデータは json_eachで参照できるので、 値を取り出すにはjson_each.valueと指定する。

少し深い階層の配列を行（レコード）として取得する

上記はシンプルな JSON 配列のみだったが、実際のデータはもっと複雑な階層構造になっていることが多い。 狙った位置の配列を取得するには JSON Path を使用する

sample テーブル

created_at	sales_log
2026-11-01	{ "store_name": "east street", "sales": ["tomato", "potato", "pumpkin"] }
2026-11-02	{ "store_name": "west river side", "sales": ["onion", "carrot"] }

上記のテーブルから、sales_log の sales 配列の要素を複数行のレコードとして取得するには、次のように SQL を書く。

SELECT
    sample.created_at
    , json_each.value
FROM
    sample
    , json_each(sample.sales_log, '$.sales')
;
                    

json_eachの第2引数に JSON Path $.salesを指定することで、 $（ルート）配下のsalesキーの値を指定している。

結果は以下の通り。

created_at	value
2026-11-01	tomato
2026-11-01	potato
2026-11-01	pumpkin
2026-11-02	onion
2026-11-02	carrot

オブジェクトの配列を展開する

json_extract関数を組み合わせることでさらに複雑な構造にも対応できる。

sample テーブル

created_at	sales_log
2026-11-01	[ { "store_name": "east street", "sales": ["tomato", "potato", "pumpkin"] }, { "store_name": "west river side", "sales": ["onion", "carrot"] } ]
2026-11-02	[ { "store_name": "east street", "sales": ["tomato", "carrot"] }, { "store_name": "mt.fuji", "sales": ["onion", "carrot", "potato"] } ]

上記のテーブルから、sales_log の store_name と sales 配列の要素を複数行のレコードとして取得するには、 次のように SQL を書く

SELECT
    created_at
    , json_extract(store.value, '$.store_name') as store_name
    , sales.value
FROM
    sample
    , json_each(sample.sales_log) AS store
    , json_each(store.value, '$.sales') AS sales
;
                    

ちょっと複雑だが、ポイントとしては以下の通り。

• json_eachで取得したテーブルに別名を付ける。
• json_eachで取得したテーブルの内容を さらにjson_eachで分解する。
• json_eachは指定された配列を再帰的に展開するわけではない。
• オブジェクトはjson_extractで展開し、値を取得する。

結果は以下のようになる

created_at	store_name	value
2026-11-01	east street	tomato
2026-11-01	east street	potato
2026-11-01	east street	pumpkin
2026-11-01	west river side	onion
2026-11-01	west river side	carrot
2026-11-02	east street	tomato
2026-11-02	east street	carrot
2026-11-02	mt.fuji	onion
2026-11-02	mt.fuji	carrot
2026-11-02	mt.fuji	potato

JSON の構造を展開するのはjson_eachと json_extractを使えばほとんどのケースで足りそう。
（ json_treeはまだ使ったことが無い・・・）

参考URL

• Table valued functions for parsing JSON: json_each(), jsonb_each(), json_tree(), and jsonb_tree()
  公式の json_each 関数に関するドキュメント
  https://www.sqlite.org/json1.html#table_valued_functions_for_parsing_json_json_each_jsonb_each_json_tree_and_jsonb_tree_

SQLite3 最後に挿入された rowid を確認する（last_insert_rowid）

概要

現在のデータベース接続で一番最後に挿入されたレコードのrowidの値を取得するには last_insert_rowid関数を使用する。

構文

last_insert_rowid()
                

実行例

環境

• Windows 11
• SQLite3 (3.53.3) Command-Line Shell

1. 最後に挿入したレコードのrowidが取得される
2. 一括で挿入した際も最後のrowidが取得される
3. 他のテーブルに挿入した場合はそちらで挿入したrowidが取得される

sqlite> create table user (id, name);
sqlite> create table product (id, name, quantity);

sqlite> -- 1.
sqlite> insert into user (id, name) values ('001', 'sample_user_001');
sqlite> select rowid, * from user;
╭───────┬───────┬─────────────────╮
│ rowid │  id   │      name       │
╞═══════╪═══════╪═════════════════╡
│     1 │ '001' │ sample_user_001 │
╰───────┴───────┴─────────────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   1 │
╰─────────────────────╯

sqlite> -- 2. 
sqlite> insert into user (id, name) values ('002', 'sample_user_002'), ('003', 'sample_user_003');
sqlite> select rowid, * from user;
╭───────┬───────┬─────────────────╮
│ rowid │  id   │      name       │
╞═══════╪═══════╪═════════════════╡
│     1 │ '001' │ sample_user_001 │
│     2 │ '002' │ sample_user_002 │
│     3 │ '003' │ sample_user_003 │
╰───────┴───────┴─────────────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   3 │
╰─────────────────────╯

sqlite> --3.
sqlite> insert into product (id, name, quantity) values ('001', 'tomato', 100);
sqlite> select rowid, * from product;
╭───────┬───────┬────────┬──────────╮
│ rowid │  id   │  name  │ quantity │
╞═══════╪═══════╪════════╪══════════╡
│     1 │ '001' │ tomato │      100 │
╰───────┴───────┴────────┴──────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   1 │
╰─────────────────────╯
sqlite>

                

last_insert_row()で取得できるのは あくまで「新規挿入」された時のrowidであり、 たとえばupdateで更新された際の値は無視される。

sqlite> select rowid, * from product;
╭───────┬───────┬────────┬──────────╮
│ rowid │  id   │  name  │ quantity │
╞═══════╪═══════╪════════╪══════════╡
│     1 │ '001' │ tomato │      100 │
╰───────┴───────┴────────┴──────────╯
sqlite> insert into user (id, name) values ('004', 'sample_user_004');
sqlite> select rowid, * from user;
╭───────┬───────┬─────────────────╮
│ rowid │  id   │      name       │
╞═══════╪═══════╪═════════════════╡
│     1 │ '001' │ sample_user_001 │
│     2 │ '002' │ sample_user_002 │
│     3 │ '003' │ sample_user_003 │
│     4 │ '004' │ sample_user_004 │
╰───────┴───────┴─────────────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   4 │
╰─────────────────────╯
sqlite> update user set rowid = '999' where id = '004';
sqlite> select rowid, * from user;
╭───────┬───────┬─────────────────╮
│ rowid │  id   │      name       │
╞═══════╪═══════╪═════════════════╡
│     1 │ '001' │ sample_user_001 │
│     2 │ '002' │ sample_user_002 │
│     3 │ '003' │ sample_user_003 │
│   999 │ '004' │ sample_user_004 │
╰───────┴───────┴─────────────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   4 │
╰─────────────────────╯
sqlite>
                

DBをattachした場合の挙動

attachコマンドを使えば複数のDBを同時に操作できる。 この場合もlast_insert_rowid()は接続先のDBを問わず、 一番 最近insertした際のrowidが返される。

C:\temp>sqlite3 test01.db
SQLite version 3.53.3 2026-06-26 20:14:12
Enter ".help" for usage hints.
sqlite> insert into user (id, name) values ('005', 'sample_user_005');
sqlite> select rowid, * from user;
╭───────┬───────┬─────────────────╮
│ rowid │  id   │      name       │
╞═══════╪═══════╪═════════════════╡
│     1 │ '001' │ sample_user_001 │
│     2 │ '002' │ sample_user_002 │
│     3 │ '003' │ sample_user_003 │
│     4 │ '004' │ sample_user_004 │
│     5 │ '005' │ sample_user_005 │
╰───────┴───────┴─────────────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   5 │
╰─────────────────────╯
sqlite> attach database "test02.db" as test02;
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   5 │
╰─────────────────────╯
sqlite> insert into test02.user (id, name) values ('006', 'sample_user_006');
sqlite> select rowid, * from test02.user;
╭───────┬───────┬─────────────────╮
│ rowid │  id   │      name       │
╞═══════╪═══════╪═════════════════╡
│     1 │ '006' │ sample_user_006 │
╰───────┴───────┴─────────────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   1 │
╰─────────────────────╯
sqlite>
                

コネクション作成直後は0になる

last_insert_rowid()はあくまで 現在の接続で最後に挿入されたデータのrowidを取得するので、 一度 接続を切ればリセットされて最初は0を返す。

sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   6 │
╰─────────────────────╯
sqlite> .quit
C:\temp>sqlite3 test01.db
SQLite version 3.53.3 2026-06-26 20:14:12
Enter ".help" for usage hints.
sqlite> select rowid, * from user;
╭───────┬───────┬─────────────────╮
│ rowid │  id   │      name       │
╞═══════╪═══════╪═════════════════╡
│     1 │ '001' │ sample_user_001 │
│     2 │ '002' │ sample_user_002 │
│     3 │ '003' │ sample_user_003 │
│     4 │ '004' │ sample_user_004 │
│     5 │ '005' │ sample_user_005 │
│     6 │ '007' │ sample          │
╰───────┴───────┴─────────────────╯
sqlite> select last_insert_rowid();
╭─────────────────────╮
│ last_insert_rowid() │
╞═════════════════════╡
│                   0 │
╰─────────────────────╯
                

参考URL

• 公式のlast_insert_rowid()に関するドキュメント
  https://sqlite.org/lang_corefunc.html#last_insert_rowid

SQLite3 rowid を理解する

概要

SQLite のテーブルの各レコードにはデフォルトで隠しカラムとしてrowidが設定される。
積極的に使うわけではないけど覚えておくと便利な機能、といった程度の認識でいい。
仕様を理解していないうちに頼ってしまうと後々痛い目に合うので注意。

このrowidには符号付64ビットのユニークな整数が自動的に格納される。
役割としてはprimary keyと同じで、テーブルのレコードを一意に識別するために使われる。

この記事ではprimary key や autoincrement を設定していないシンプルなテーブルでの挙動について記載する。

実行例

環境

• Windows 11
• SQLite3 (3.53.1) Command-Line Shell

適当なテーブルを作成し、適当なデータを挿入し、rowidを確認してみる。

rowidに入っている値を調べるには、以下のいずれかのカラム名を指定してselect文を実行する。

• rowid
• oid
• _rowid_

1. アスタリスク（*）では rowidselect されない。
2. 明示的にrowidを指定すると select することができる。
3. rowid、 oid、 _rowid_はすべて同じ値を指し、 カラム名はrowidとして返される。

sqlite> create table user (id, name);
sqlite> insert into user (id, name) values (111, 'sample_user_1');
sqlite> insert into user (id, name) values (222, 'sample_user_2');
sqlite> .mode box

sqlite> -- 1.
sqlite> select * from user;
┌─────┬───────────────┐
│ id  │     name      │
├─────┼───────────────┤
│ 111 │ sample_user_1 │
│ 222 │ sample_user_2 │
└─────┴───────────────┘

sqlite> -- 2.
sqlite> select rowid, * from user;
┌───────┬─────┬───────────────┐
│ rowid │ id  │     name      │
├───────┼─────┼───────────────┤
│ 1     │ 111 │ sample_user_1 │
│ 2     │ 222 │ sample_user_2 │
└───────┴─────┴───────────────┘

sqlite> -- 3.
sqlite> select rowid, oid, _rowid_ from user;
┌───────┬───────┬───────┐
│ rowid │ rowid │ rowid │
├───────┼───────┼───────┤
│ 1     │ 1     │ 1     │
│ 2     │ 2     │ 2     │
└───────┴───────┴───────┘
                

要するに上記のSQLでは、実際には以下のようなテーブルが作成されていることになる。

rowid 別名：oid 別名：_rowid_ （隠しカラム）	id	name
1	111	sample_user_1
2	222	sample_user_2

そのテーブルの最大 rowid + 1 が採番される

例外はあるものの、基本的にはそのテーブルの最大 rowid + 1 が採番される。
そのため、rowid は連番であることが多い。

参考URL

• ROWIDs and the INTEGER PRIMARY KEY(英語)
  公式のROWIDに関するドキュメント
  https://www.sqlite.org/lang_createtable.html#rowids_and_the_integer_primary_key
• Rowid Tables（英語）
  Rowid が設定されたテーブルに関するドキュメント
  https://sqlite.org/rowidtable.html

SQLite3 テーブルに主キーを設定する(PRIMARY KEY)

概要

SQLite3 でカラムに主キーを設定するにはいくつか方法があるので、 この記事ではその方法について記載する。

環境

• Windows 11
• SQLite3 (3.53.1) Command-Line Shell

テーブル作成時にカラムに続けて主キーを指定する

例として以下のような、idを主キーとする user テーブルを作成する。

「user」テーブル

カラム名	型	制約
id	text	主キー、 not null制約
name	text

このテーブルを作成するためには以下のSQLを実行する。
カラム名の後にPRIMARY KEYと記述することで、そのカラムに主キーを設定出来る。

create table user (
    id     text  primary key not null
    , name text
);
                

上記のSQLを実行すると、idカラムに主キーが設定される。
そのため、idカラムには重複する値を登録することは出来なくなる。

以下は、上記のテーブルにデータを登録する例。 idカラムに重複する値を登録しようするとエラーになる。

sqlite> insert into user (id, name) values ('001', 'sample_name_1');
sqlite> insert into user (id, name) values ('002', 'sample_name_2');
sqlite> insert into user (id, name) values ('001', 'sample_name_3');
Runtime error: UNIQUE constraint failed: user.id (19)                 
                

補足

上記の方法で複数カラムを主キーとする、いわゆる「複合キー」を設定しようとするとエラーになる。 複合キーを設定するには次項の「テーブル作成時にPRIMARY KEY構文を使用する」を参照のこと。

以下は、複合キーを設定しようとしてエラーになる例。

sqlite> create table user (
(x1...>     id     text primary key not null
(x1...>     , name text primary key
(x1...> );
Parse error: table "user" has more than one primary key
                    

テーブル作成時にPRIMARY KEY構文を使用する

カラム名に続けてPRIMARY KEYを指定するのではなく、テーブル作成分の後半にPRIMARY KEY構文を使用して主キーを設定することも出来る。
この方法を使用することで、複数カラムを主キーとする「複合キー」を設定することが出来る。

以下は、PRIMARY KEY構文を使用して主キーを設定する例。

1. idカラムを主キーとする場合
2. idカラムとnameカラムを複合キーとして主キーに設定する場合

-- 1.
create table user (
  id     text not null
  , name text
  , primary key (id)
);

-- 2.
create table user (
  id     text not null
  , name text not null
  , primary key ( id, name )
);
                

複合キーは複数のカラムの値の組み合わせでユニークになることを保証するため、 複合キーに設定したカラムのいずれかに重複する値があっても、他のカラムの値が異なれば登録することが出来る。

以下は、複合キーを設定したテーブルにデータを登録する例。

sqlite> create table user (
(x1...>   id text  not null
(x1...>   , name text not null
(x1...>   , primary key ( id, name )
(x1...> );
sqlite> insert into user (id, name) values ('001', 'sample_name_1');
sqlite> insert into user (id, name) values ('001', 'sample_name_2');
sqlite> insert into user (id, name) values ('001', 'sample_name_1');
Runtime error: UNIQUE constraint failed: user.id, user.name (19)
                

テーブル作成後に主キーを設定する

SQLite3 では、既存のテーブルに対して主キーを ALTER TABLE等で追加設定することは出来ない。 そのため、既存のテーブルに主キーを設定するには、以下の手順で行う必要がある。

1. 新しいテーブルを作成する
2. 既存のテーブルから新しいテーブルにデータをコピーする
3. 既存のテーブルを削除する

詳しい作業手順は過去記事のカラムの型を変更する手順と同じになる。
結構手間がかかるので、最初のテーブル設計はきっちりやるべし。

• SQLite3 カラムの型を変更する

主キーを設定するカラムには必ず NOT NULL 制約を設定する

注意事項として、SQLite3 では PRIMARY KEYを指定しただけでは NULLを入力することができてしまう。

以下はidカラムに主キーを設定したテーブルにNULLを登録する例。

「user」テーブル

カラム名	型	制約
id	text	主キー
name	text

sqlite> .mode box
sqlite> .nullvalue [null]
sqlite> create table user (
(x1...>   id     text primary key
(x1...>   , name text
(x1...> );
sqlite> insert into user (id, name) values (NULL, 'sample_name_1');
sqlite> insert into user (id, name) values (null, 'sample_name_2');
sqlite> select * from user;
┌────────┬───────────────┐
│   id   │     name      │
├────────┼───────────────┤
│ [null] │ sample_name_1 │
│ [null] │ sample_name_2 │
└────────┴───────────────┘
                

• SQLite 3 コマンドラインツールでselectの結果を見やすくする
• SQLite 3 コマンドラインツールでnullの表示を見やすくする

上記の例では、idカラムに主キーを設定しているにも関わらず、 NULLを登録することが出来てしまっている。 さらに、NULLであれば複数行登録することも出来てしまっている。
そのため、主キーを設定するカラムにはNOT NULL制約を設定したほうがよい。

これは過去バージョンで主キーにNULLが登録出来てしまっていたことに起因している。 SQLite は後方互換性を重視しているため修正はせず、NULLが登録できる状態を維持する方針らしい。

また、上記の例では主キーのデータ型がtextのためこのような挙動になっているが、 integer型の主キーの場合、もうちょっと厄介な挙動をする。
そのため、主キーを設定するカラムにはNOT NULL制約は必須という考えでいたほうが安全。

参考URL

• PRIMARY KEY(英語)
  公式のPRIMARY KEYに関するドキュメント
  https://www.sqlite.org/lang_createtable.html#the_primary_key
• NOT NULL constraints(英語)
  公式のNOT NULL制約に関するドキュメント
  https://www.sqlite.org/lang_createtable.html#not_null_constraints
• column-constraint(英語)
  公式のカラムに設定する各種制約に関するドキュメント
  https://www.sqlite.org/syntax/column-constraint.html

• SQLite3 備忘録：SQLite3 nullを登録できないようにカラムを設定する(NOT NULL制約)
  https://sfnovicenotes.blogspot.com/2022/04/sqlite3-nullnot-null.html
• SQLite3 備忘録：SQLite3 カラムの型を変更する
  https://sfnovicenotes.blogspot.com/2021/01/sqlite3.html