JSON を照会する

D1 は、データベースに保存した JSON データの照会と解析を組み込みでサポートしています。次のことができます。

• 保存した JSON オブジェクト内の パスを照会 する。たとえば、名前付きキーや配列インデックスの値を直接取り出せます。大きな JSON オブジェクトで特に便利です。
• オブジェクトまたは配列内の値を挿入、または置き換える。
• JSON オブジェクトや配列の内容を 複数行に展開 する。たとえば WHERE ... IN 述語の一部として使います。
• 挿入した JSON オブジェクトの値で自動入力される 生成列 を作成する。

D1 内で JSON を直接解析する最大の利点のひとつは、データベースへの往復（クエリ）回数を減らせることです。JSON オブジェクトをアプリケーションへ読み込み（1）、解析し、書き戻す（2）必要が減ります。

より精密にデータを照会でき、アプリケーション側で追加の解析や絞り込みが必要な結果セットも小さくなります。

JSON データは D1 では TEXT 列として保存されます。JSON の型は、D1 全般と同じ 型変換ルール に従います。次のとおりです。

• JSON の null は D1 の NULL として扱われます。
• JSON の数値は INTEGER または REAL として扱われます。
• 真偽値は INTEGER として扱われます。true は 1、false は 0 です。
• オブジェクトと配列の値は TEXT です。

次の表は、D1 に組み込まれている JSON 関数と使用例です。

• json 引数のプレースホルダーには、JSON オブジェクト、配列、文字列、数値、または null を渡せます。
• value 引数は文字列リテラルだけを受け付け、入力が整形式の JSON でも文字列として扱います。例外は json_* 関数を入れ子にするときです。外側（ラップ側）の関数は、内側（ラップされる側）の戻り値を JSON として解釈します。
• path 引数はパス形式の走査構文を受け付けます。たとえば $ は最上位のオブジェクト / 配列、$.key1.key2 は入れ子のオブジェクト、$.key[2] は配列のインデックスです。

D1 が依拠する SQLite の JSON 拡張 ↗ には、追加の使用例もあります。

JSON 関数は、JSON でないデータや無効な JSON を操作すると malformed JSON エラーを返します。D1 が有効とみなす JSON は RFC 7159 ↗ に準拠したものです。

次の例では、文字列（有効な JSON ではない）に対して json_extract を呼ぶと、クエリは malformed JSON エラーを返します。

SELECT json_extract('not valid JSON: just a string', '$')

次のエラーが返ります。

ERROR 9015: SQL engine error: query error: Error code 1: SQL error or missing database (malformed
  JSON)`

D1 の 生成列 サポートでは、他の列の値（JSON データの抽出値や計算値を含む）から動的に生成される列を作れます。

これらの列は他の列と同じように照会でき、インデックス も定義できます。頻繁に照会・絞り込む JSON データがある場合、生成列とインデックスを作ると、クエリ性能が大きく上がることがあります。

たとえば、大きな JSON オブジェクト内の値に基づく列を定義するには、AS キーワードと JSON 関数 を組み合わせて、型付きの列を生成します。

CREATE TABLE some_table (
    -- other columns omitted
    raw_data TEXT -- JSON: {"measurement":{"aqi":[21,42,58],"wind_mph":"13","location":"US-NY"}}
    location AS (json_extract(raw_data, '$.measurement.location')) STORED
)

生成列の詳細は Generated columns を参照してください。

D1 で JSON オブジェクトから値を抽出する方法は 3 つあります。

• json_extract() 関数。例: json_extract(text_column_containing_json, '$.path.to.value)。
• -> 演算子。値の JSON 表現を返します。
• ->> 演算子。値の SQL 表現を返します。

-> と ->> は、PostgreSQL および MySQL / MariaDB の同名演算子と同様に動作します。

sensor_reading という列に次の JSON オブジェクトがあるとき、値を直接抽出できます。

{
    "measurement": {
        "temp_f": "77.4",
        "aqi": [21, 42, 58],
        "o3": [18, 500],
        "wind_mph": "13",
        "location": "US-NY"
    }
}

-- Extract the temperature value
json_extract(sensor_reading, '$.measurement.temp_f')-- returns "77.4" as TEXT

-- Extract the maximum PM2.5 air quality reading
sensor_reading -> '$.measurement.aqi[3]' -- returns 58 as a JSON number

-- Extract the o3 (ozone) array in full
sensor_reading -\-> '$.measurement.o3' -- returns '[18, 500]' as TEXT

JSON 配列の長さは、次の 2 とおりで取得できます。

1. json_array_length(value) を直接呼ぶ
2. json_array_length(value, path) を呼び、オブジェクトまたは外側の配列内の配列へのパスを指定する

たとえば、login_history という列に次の JSON オブジェクトがあるとき、直近のログイン回数を直接取得できます。

{
    "user_id": "abc12345",
    "previous_logins": ["2023-03-31T21:07:14-05:00", "2023-03-28T08:21:02-05:00", "2023-03-28T05:52:11-05:00"]
}

json_array_length(login_history, '$.previous_logins') --> returns 3 as an INTEGER

より複雑なクエリの述語としても json_array_length を使えます。例: WHERE json_array_length(some_column, '$.path.to.value') >= 5。

既存の JSON オブジェクトや配列へ値を挿入するには、json_insert() を使います。たとえば、users テーブルの login_history という TEXT 列に、次のオブジェクトがあるとします。

{"history": ["2023-05-13T15:13:02+00:00", "2023-05-14T07:11:22+00:00", "2023-05-15T15:03:51+00:00"]}

login_history 列内の history 配列へ新しいタイムスタンプを追加するには、次のようなクエリを書きます。

UPDATE users
SET login_history = json_insert(login_history, '$.history[#]', '2023-05-15T20:33:06+00:00')
WHERE user_id = 'aba0e360-1e04-41b3-91a0-1f2263e1e0fb'

json_insert には 3 つの引数を渡します。

1. 変更したい JSON が入っている列名。
2. 変更するオブジェクト内のキーへのパス。
3. 挿入する JSON 値。[#] を使うと、配列の末尾に追加します。

既存の値を置き換えるには json_replace() を使います。既存のキーと値のペアがあれば上書きします。存在するかどうかに関係なく値を設定するには、json_set() を使います。

配列を複数行に展開するには json_each を使います。複数の値に対して WHERE column IN (?) クエリを組み立てるときに便利です。たとえば整数の id でユーザー一覧を更新したい場合、json_each で各値を value 列とするテーブルを返します。

UPDATE users
SET last_audited = '2023-05-16T11:24:08+00:00'
WHERE id IN (SELECT value FROM json_each('[183183, 13913, 94944]'))

json_each が返すテーブルから value 列だけを取り出し、各行が配列として渡したユーザー ID になります。

json_each は実質的に複数列のテーブルを返します。特に重要な列は次のとおりです。

• key - キー（またはインデックス）。
• value - json_each が解析した各要素のリテラル値。
• type - 値の型。null、true、false、integer、real、text、array、object のいずれかです。
• fullkey - 要素への完全なパス。例: 配列の 2 番目の要素は $[1]、入れ子のオブジェクトは $.path.to.key。
• path - 最上位のパス。fullkey が $[0] の要素では $ です。

この例では、SELECT * FROM json_each('[183183, 13913, 94944]') は次のようなテーブルを返します。

key|value|type|id|fullkey|path
0|183183|integer|1|$[0]|$
1|13913|integer|2|$[1]|$
2|94944|integer|3|$[2]|$

Worker 内の D1 Workers Binding API でも json_each を使えます。ステートメントを作り、JSON.stringify で配列を バインドパラメーター として渡します。

const stmt = context.env.DB
    .prepare("UPDATE users SET last_audited = ? WHERE id IN (SELECT value FROM json_each(?1))")
const resp = await stmt.bind(
    "2023-05-16T11:24:08+00:00",
    JSON.stringify([183183, 13913, 94944])
    ).run()

これで、users テーブルのうち id が渡した 3 つのいずれかに一致する行だけが更新されます。