PIやCADから受け取るデータは、fieldが増減するためJSONで保存したい一方、検索や一意性判定にはSQLを使いたいことがあります。すべてを列へ分解するとschema変更が増え、JSON文字列をそのままTEXT列へ入れるだけでは、不正JSON、型違い、必須field欠落をDBが防げません。
SQLiteのJSON関数と生成列を組み合わせると、元のJSONを保持しながら、重要なfieldだけを検証・検索できます。本記事では、json_valid()、json_extract()、json_type()、生成列、INDEXを一つの保存契約として設計します。
SQLiteへJSONを保存する目的
JSON保存が向くのは、object全体を再出力したい、任意fieldを残したい、source systemのpayloadを監査したい場面です。一方、ID、version、statusなど頻繁に検索・結合する値までJSON内部だけに置くと、制約と索引を設計しにくくなります。
実務では、次のhybrid構造が扱いやすくなります。
- 識別・同期制御に使う値は通常列へ置く
- 可変属性と原文はJSONのTEXT列へ置く
- 検索頻度が高いJSON fieldは生成列へ投影する
CHECKでJSON構文、必須field、型を検証する- 生成列にINDEXを作り、通常列と同じ形で検索する
JSONを「何でも入る箱」ではなく、DB schemaの内側にある構造化documentとして扱う設計です。
最小のJSONデータ例
設計資産の可変属性を次のobjectで受け取るとします。
{
"schema_version": 1,
"kind": "hydraulic-cylinder",
"revision": "R3",
"mass_kg": 12.4,
"released": false,
"tags": ["hydraulic", "prototype"]
}schema_version、kindなどがkey、右側がvalueです。object全体が一件のpayloadで、tagsはstringを要素に持つarrayです。
asset_idと更新順序を示すsource_versionは、JSONの外に通常列として保存します。これにより、payloadの表記やfield順に依存せず、一意性と差分更新を判定できます。
JSON構文と型をDBで検証する
次のtableは、元JSONをpayloadへ保存し、検索対象をVIRTUAL生成列へ投影します。
CREATE TABLE assets (
asset_id TEXT PRIMARY KEY NOT NULL,
source_version INTEGER NOT NULL CHECK (source_version >= 1),
payload TEXT NOT NULL CHECK (json_valid(payload)),
schema_version INTEGER GENERATED ALWAYS AS (
json_extract(payload, '$.schema_version')
) VIRTUAL NOT NULL,
kind TEXT GENERATED ALWAYS AS (
json_extract(payload, '$.kind')
) VIRTUAL NOT NULL,
revision TEXT GENERATED ALWAYS AS (
json_extract(payload, '$.revision')
) VIRTUAL NOT NULL,
mass_kg REAL GENERATED ALWAYS AS (
json_extract(payload, '$.mass_kg')
) VIRTUAL,
released INTEGER GENERATED ALWAYS AS (
json_extract(payload, '$.released')
) VIRTUAL NOT NULL,
CHECK (json_type(payload, '$.schema_version') = 'integer'),
CHECK (json_type(payload, '$.kind') = 'text'),
CHECK (json_type(payload, '$.revision') = 'text'),
CHECK (
json_type(payload, '$.mass_kg') IN ('integer', 'real', 'null')
OR json_type(payload, '$.mass_kg') IS NULL
),
CHECK (json_type(payload, '$.released') IN ('true', 'false'))
);
CREATE INDEX assets_kind_revision_idx
ON assets(kind, revision);json_valid(payload)の引数を一つにすると、厳密なRFC 8259形式のJSON textだけを有効と判定します。コメント、末尾comma、single quoteなどのJSON5拡張を受け入れたい場合は別のflagを指定できますが、外部連携では受理範囲を意図せず広げない方が契約を明確にできます。
json_extract()はJSONのscalarをSQLiteの値へ変換します。JSON numberはINTEGERまたはREAL、stringはTEXT、trueとfalseは1と0、JSON nullはSQL NULLになります。objectやarrayを取り出した場合はJSON textです。
NULLとfield欠落を区別する
json_extract()だけを見ると、JSON nullとfield欠落はどちらもSQL NULLになります。
SELECT
json_extract('{"mass_kg": null}', '$.mass_kg') AS explicit_null,
json_extract('{}', '$.mass_kg') AS missing;意味を分けるにはjson_type()を使います。
| JSONの状態 | json_typeの結果 | json_extractの結果 |
|---|---|---|
"mass_kg": 12.4 | real | 12.4 |
"mass_kg": null | null | SQL NULL |
| fieldなし | SQL NULL | SQL NULL |
「未計測」をJSON null、「このschemaでは未提供」をfield欠落とするなら、applicationとDBの両方で区別します。必須fieldは生成列のNOT NULLまたはjson_type()のCHECKで欠落を拒否します。
生成列とINDEXで検索する
VIRTUAL生成列は、rowを読むときに式から値を計算します。SQLite公式仕様では生成列にINDEXを作成できるため、JSON pathをapplicationごとに書き直さず、通常列のように検索できます。
SELECT asset_id, revision, mass_kg
FROM assets
WHERE kind = ? AND revision = ?;このqueryはassets_kind_revision_idxの対象です。生成列を使わず式INDEXを直接作る方法もあります。
CREATE INDEX assets_kind_expr_idx
ON assets(json_extract(payload, '$.kind'));ただしSQLiteの式INDEXは、query側に同じ式が現れるときに利用されます。path表記や式を各所で重複させるより、意味のある生成列名へまとめる方が保守しやすくなります。
VIRTUAL列はALTER TABLE ADD COLUMNで追加できますが、STORED生成列は同じ方法では追加できません。運用中のDBへ検索fieldを増やす場合は、対応するSQLite versionとmigration方法を事前に確認します。
arrayはjson_eachで展開する
tagsのようなarrayは、json_each()で一要素一rowへ展開できます。
SELECT DISTINCT a.asset_id
FROM assets AS a
JOIN json_each(a.payload, '$.tags') AS tag
WHERE tag.value = ?;少量dataの補助検索には便利ですが、array要素を通常のINDEXで直接検索する設計ではありません。tagによる検索・集計・参照整合性が中心になるなら、asset_tags(asset_id, tag)のような子tableへ正規化し、PRIMARY KEY (asset_id, tag)とINDEXを付けます。
一つの値をどちらにも保存する場合は、JSONと子tableの更新を同じtransactionで行い、どちらを正とするか決めます。
Pythonで検証・保存・検索する
Python標準libraryだけで、JSON生成からSQLite保存まで実装できます。
import json
import sqlite3
UPSERT_SQL = """
INSERT INTO assets (asset_id, source_version, payload)
VALUES (?, ?, ?)
ON CONFLICT(asset_id) DO UPDATE SET
source_version = excluded.source_version,
payload = excluded.payload
WHERE excluded.source_version > assets.source_version
"""
def encode_payload(data: dict) -> str:
return json.dumps(
data,
ensure_ascii=False,
allow_nan=False,
sort_keys=True,
separators=(",", ":"),
)
def save_asset(
conn: sqlite3.Connection,
asset_id: str,
source_version: int,
data: dict,
) -> bool:
payload = encode_payload(data)
cursor = conn.execute(
UPSERT_SQL,
(asset_id, source_version, payload),
)
conn.commit()
return cursor.rowcount == 1
def find_assets(
conn: sqlite3.Connection,
kind: str,
revision: str,
) -> list[tuple]:
return conn.execute(
"""
SELECT asset_id, source_version, mass_kg, released
FROM assets
WHERE kind = ? AND revision = ?
ORDER BY asset_id
""",
(kind, revision),
).fetchall()allow_nan=Falseは、PythonのNaNやInfinityをJSON numberとして出力しないための指定です。sort_keys=Trueと余分な空白を除くseparatorsにより、同じ構造を安定した表記へ寄せられます。ただし、これを暗号学的なJSON正規化規格の代わりにはしません。
SQLへ値を渡すときは?placeholderを使い、JSON文字列や検索値をSQLへ連結しません。pathを利用者入力から自由に指定させる場合も、許可したpathとの対応表をapplication側で持ちます。
正常系と異常系をtestする
最低限、次のcaseを自動testへ含めます。
- 正しいobjectを保存し、生成列から型付き値を取得できる
- JSON構文エラーを拒否する
kind欠落、revisionの数値化、releasedの文字列化を拒否するmass_kgのJSON nullとfield欠落を区別できる- 同じ
source_versionの再送では既存rowを変更しない - 古い
source_versionが新しいpayloadを上書きしない - 新しいversionでは一回だけ更新する
EXPLAIN QUERY PLANで想定INDEXの利用を確認する
DBのconstraint違反はsqlite3.IntegrityError、不正JSONの評価は環境や式によってsqlite3.OperationalErrorになる場合があります。公開APIでは内部例外をそのまま返さず、field、理由、schema versionを持つ検証errorへ変換します。
冪等性とschema変更
例のUPSERTは、source_versionが現在値より大きいときだけ更新します。同じversionの再送や遅れて届いた古いeventでは最終状態が変わらないため、連携処理を再実行できます。
ただしschema_versionとsource_versionは別物です。
schema_version：JSON fieldと意味の契約版source_version：同じ資産が更新された順序
JSONへ新しい任意fieldを追加するだけなら既存readerは無視できます。必須field化、型変更、意味変更ではschema versionを上げ、旧payloadを読む変換処理を用意します。生成列やINDEXの追加もDB migrationとしてversion管理します。
セキュリティと運用上の注意
JSONを保存できることと、安全に保存できることは別です。
- payload byte数とnestingを入口で制限する
- API key、token、credentialをpayloadへ含めない
- 個人情報と公開可能な設計属性を別tableまたは別DBへ分離する
- validation errorへpayload全文を出さない
- SQL値はplaceholderでbindする
- 動的なJSON path、列名、ORDER BYはallowlistから選ぶ
- backup、暗号化、file permission、保存期間をJSON以外の列と同様に管理する
SQLite公式資料では、深さ1000を超えるJSONを無効としています。application側にも用途に合う小さい上限を設け、DB parserの限界を入力制限として利用者へ直接露出させない方が安全です。
CAD・API連携への再利用
CAD属性、解析条件、外部APIの追加fieldは変化しやすいため、元JSONを保持する価値があります。一方、asset ID、source version、revision、部品種別などを通常列または生成列へ投影すれば、DB検索と再同期を安定させられます。
将来、別DBや検索systemへ移す場合も、JSON原文、schema version、業務ID、更新順序が分離されていれば再変換できます。頻繁に結合・集計するfieldは通常列へ昇格し、まれに参照する属性はJSONへ残すという基準を明文化します。
まとめ
SQLiteへJSONを保存するときは、TEXT列へ入れるだけで完了ではありません。
json_valid()で保存可能なJSON構文を固定するjson_type()で必須fieldと型を検証する- JSON nullとfield欠落を区別する
- IDと更新versionは通常列として保持する
- 検索対象を生成列へ投影し、INDEXを作る
- arrayの検索が中心なら子tableへの正規化を検討する
- parameter bindingでSQL injectionを防ぐ
- source version付きUPSERTで再送と順序逆転に備える
- schema versionとdataの更新versionを分ける
元JSONの柔軟性とrelational schemaの制約を組み合わせれば、API、Python、CAD、SQLite間で、壊れたpayloadを拒否しながら再利用可能なデータ資産を作れます。
