この記事について
この記事は、生成AIを活用した自動生成フローで作成しています。SQLite公式JSON関数仕様を確認し、サンプルデータは架空の注文です。
検証：公式仕様確認済み／SQLite実機での実行は未確認
情報確認日：2026年10月11日。以下の出力は期待例です。
JSONの配列が1つのセルに入っていると、通常のSQLでは商品ごとの集計が難しく見えます。SQLiteの json_each() を使えば、配列の各要素を仮想的な行に展開して、SUM() や GROUP BY で集計できます。元データを更新する必要はありません。
ここでは、注文テーブルの items 列に「商品名・数量・単価」の配列が入っている状況を考えます。注文別の明細を取り出し、商品別売上を求め、JSONが壊れている場合や空配列の場合まで確認します。
JSON配列を行へ展開する理由
たとえば1注文に商品が2つ含まれるとき、JSONは次のようになります。
[
{"sku":"pen","qty":2,"price":120},
{"sku":"note","qty":1,"price":350}
]
人が読むには分かりやすい構造ですが、SQLの通常の列としては sku や qty が独立していません。文字列をカンマで分割する方法は、JSONの引用符・エスケープ・入れ子に弱く、避けるべきです。
json_each(items) はJSON配列の直下にある要素を1件ずつ返すテーブル値関数です。各要素がJSONオブジェクトなら、返された value に対して json_extract(value, '$.sku') のように必要な値を取り出せます。
flowchart LR
A["orders: 1行にitems配列"] --> B["json_each(items)"]
B --> C["商品明細: 1要素=1行"]
C --> D["json_extractでsku/qty/price取得"]
D --> E["GROUP BYとSUMで集計"]
この変換はSELECT中だけの処理です。元のJSONやテーブルの内容を分解して保存し直すわけではありません。
準備：架空の注文を作る
SQLite 3.38.0以降ではJSON関数が標準で組み込まれています。ただしビルド時に SQLITE_OMIT_JSON を指定した構成は例外です。以下はSQLiteのコマンドラインクライアントで実行するSQLです。
CREATE TEMP TABLE orders (
order_id INTEGER PRIMARY KEY,
items TEXT NOT NULL
);
INSERT INTO orders (order_id, items) VALUES
(101, '[{"sku":"pen","qty":2,"price":120},{"sku":"note","qty":1,"price":350}]'),
(102, '[{"sku":"pen","qty":3,"price":120}]'),
(103, '[]');
TEMP TABLE を使うのは、練習用のデータを既存の業務テーブルへ混ぜないためです。新しい空のSQLiteデータベースで実行してください。テーブルは接続が終了すると消えます。
注文101にはペン2本とノート1冊、102にはペン3本、103には商品がありません。数量と単価は整数とし、税・値引き・返品は扱いません。
まず1件を展開して観察する
SELECT
o.order_id,
j.key AS array_index,
json_extract(j.value, '$.sku') AS sku,
json_extract(j.value, '$.qty') AS qty,
json_extract(j.value, '$.price') AS price
FROM orders AS o
JOIN json_each(o.items) AS j
WHERE o.order_id = 101
ORDER BY j.key;
期待されるのは、注文101から2行が返ることです。配列の添字 key は0と1になり、sku はそれぞれ pen と note です。json_each() は配列の要素を走査し、json_extract() は要素内の指定されたパスから値を取り出します。
ここでの JOIN は、注文の各行に含まれるJSONを、その行に対応する要素の集合へ展開する役目です。SQL上では json_each(o.items) が左側の o.items を参照します。
JSONが空配列の注文103は、json_each() が要素を返さないため、このJOINの結果には現れません。商品がない注文を残したい場合は、後述のLEFT JOINが必要です。
商品別の売上を集計する
展開した要素の数量と単価を掛け、その合計を商品コードごとに求めます。
SELECT
json_extract(j.value, '$.sku') AS sku,
SUM(
json_extract(j.value, '$.qty') *
json_extract(j.value, '$.price')
) AS sales
FROM orders AS o
JOIN json_each(o.items) AS j
GROUP BY json_extract(j.value, '$.sku')
ORDER BY sku;
期待結果は note = 350、pen = 600 です。ペンは注文101の2本と102の3本で合計5本、単価120なので600になります。
ここでは数量・単価が数値であることを前提としています。現実のJSONには "qty":"2" のような文字列、null、項目欠落、負数などが混ざる可能性があります。SQLiteには型変換規則がありますが、業務上の金額計算で暗黙の変換に依存するのは危険です。
実務では json_type(j.value,'$.qty')='integer' のような型検査や、数量が0以上か、単価が許容範囲か、といった条件を先に決めます。少数の金額は丸め誤差を避けるため、通貨の最小単位を整数で扱う設計も検討してください。
空配列の注文も一覧へ残す
注文単位で件数を調べる場合、空配列を消してしまうと「注文がなかった」のか「商品が0件だった」のか区別できません。
SELECT
o.order_id,
COUNT(j.key) AS item_count
FROM orders AS o
LEFT JOIN json_each(o.items) AS j
GROUP BY o.order_id
ORDER BY o.order_id;
期待結果は注文101が2、102が1、103が0です。LEFT JOIN は一致する要素がなくても左側の注文を残します。COUNT(*) にすると、空配列でもLEFT JOINが生成した行を数えて1になるため、ここでは COUNT(j.key) を使います。
なお、item_count は商品の種類の要素数です。qty の合計とは違います。注文101は2明細ですが、数量は合計3です。どちらを求めているかを区別してください。
壊れたJSONがあるときの対処
JSON関数は、入力が不正なJSONだとエラーになることがあります。外部システムから受け取った文字列を無条件で json_each() に渡すのは避けます。
最初に読み取り専用で確認します。
SELECT order_id, json_valid(items) AS valid_json FROM orders ORDER BY order_id;
今回の3件はいずれも1になる想定です。json_valid() は通常のJSON文字列の妥当性確認に利用できます。ただし、妥当なJSONでもトップレベルが配列とは限りません。
配列だけを展開したい場合は、次のように入力を正規化してから関数へ渡す方法があります。
SELECT
o.order_id,
j.key,
j.value
FROM orders AS o
JOIN json_each(
CASE
WHEN json_valid(o.items) AND json_type(o.items) = 'array'
THEN o.items
ELSE '[]'
END
) AS j;
不正なJSONや配列でない値は空配列として扱うため、集計処理を継続できます。ただし不正データを正常データと見なしてよいわけではありません。業務では不正レコードを別途抽出し、原因を調べ、処理対象から除外した件数を記録する必要があります。
json_eachとjson_treeはどう違う？
json_each() は指定した配列やオブジェクトの直下の要素を返します。今回のように「配列の各商品」を処理する用途に向きます。一方、json_tree() は子孫を再帰的に走査するため、入れ子の奥にある値まで検索したい場合に役立ちます。
深い階層を単純に json_tree() で展開すると、オブジェクト自身の行や中間ノードも含まれます。商品行だけを数えたい処理には余計な行が混ざる可能性があります。まず必要な階層を決め、直下だけで足りるなら json_each() を選びます。
json_extract() だけでも $[0].sku のように特定位置の値を取得できます。しかし配列の長さが変わると、0番目・1番目を手書きする方法は保守が難しくなります。配列の長さが不定なら json_each() のほうが自然です。
運用上の注意と設計判断
JSONを列に保存すること自体が悪いわけではありません。 外部APIから受け取った柔軟な属性や、たまに参照する追加情報なら便利です。ただし商品明細を頻繁に集計し、検索条件や整合性制約を掛けるなら、明細を別テーブルへ正規化したほうが設計しやすい場合があります。
毎回JSONを展開すると解析コストが掛かります。大量データでは、検索対象の項目を通常の列や生成列として持つ方法、インデックス、明細テーブルへの分離を検討します。実際の速度は件数、JSONサイズ、SQL、SQLiteのバージョンに依存するため、EXPLAIN QUERY PLAN と実測で判断してください。
今回のSQLはSELECTだけなら読み取り専用ですが、練習用の CREATE TEMP TABLE と INSERT は一時テーブルを作成します。本番データベースに対して練習するのではなく、空の検証用データベースで試してください。
最後に INSERT INTO orders VALUES (104, '[{"sku":"note","qty":2,"price":350}]'); を追加して、商品別売上をもう一度集計してみましょう。ノートの売上が350から1050へ変わるはずです。この変化を自分で確認すると、JSONの配列を行に変換してから集計する流れが理解できます。
公式資料
SQLite JSON Functions And Operators — json_each、json_extract、json_valid、json_treeと組込み条件
SQLite SELECT — JOIN、GROUP BY、集計の基本
SQLite Aggregate Functions — COUNT、SUMの動作
本記事のSQLはSQLite公式仕様に基づく教材です。SQLite実機での実行確認は行っていないため、掲載した数値はサンプルデータから計算した期待値です。
