SQLite 備忘録: json

概要

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"] } ]

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_

SQLite 備忘録

2026年11月6日金曜日

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

概要

構文

使用例

環境