2026年11月6日金曜日

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 については以下の記事を参照。

この関数は 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

Posted by at
Labels: , ,