クエリ JSON

D1は、データベース内に保存されたJSONデータのクエリと解析をサポートしています。これにより、次のことが可能になります。

保存されたJSONオブジェクト内のクエリパス - たとえば、名前付きキーや配列インデックスの値を直接抽出することができ、特に大きなJSONオブジェクトで便利です。
オブジェクトや配列内の値を挿入および/または置き換えます。
JSONオブジェクトの内容を展開したり、配列を複数の行に展開したりします - たとえば、WHERE ... IN述語の一部として使用するためです。
挿入したJSONオブジェクトから自動的に値が埋め込まれる生成列を作成します。

D1内でJSONを直接解析する最大の利点の1つは、データベースへの往復（クエリ）の回数を直接減少させることができることです。これにより、アプリケーションに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]は配列のインデックスを指定します。

関数	説明	例
`json(json)`	提供された文字列がJSONであることを検証し、そのJSONオブジェクトのミニファイドバージョンを返します。	`json('{"hello":["world" ,"there"] }')`は`{"hello":["world","there"]}`を返します。
`json_array(value1, value2, value3, ...)`	値からJSON配列を返します。	`json_array(1, 2, 3)`は`[1, 2, 3]`を返します。
`json_array_length(json)` - `json_array_length(json, path)`	JSON配列の長さを返します。	`json_array_length('{"data":["x", "y", "z"]}', '$.data')`は`3`を返します。
`json_extract(json, path)`	指定されたパスでの値を`$.path.to.value`構文を使用して抽出します。	`json_extract('{"temp":"78.3", "sunset":"20:44"}', '$.temp')`は`"78.3"`を返します。
`json -> path`	指定されたパスでの値を抽出し、JSONとして返します。
`json ->> path`	指定されたパスでの値を抽出し、SQL型として返します。
`json_insert(json, path, value)`	指定されたパスに値を挿入します。既存の値を上書きしません。
`json_object(label1, value1, ...)`	(キー、値)のペアを受け入れ、JSONオブジェクトを返します。	`json_object('temp', 45, 'wind_speed_mph', 13)`は`{"temp":45,"wind_speed_mph":13}`を返します。
`json_patch(target, patch)`	提供されたパッチをターゲットJSONオブジェクトにマージするためにJSON MergePatch ↗アプローチを使用します。
`json_remove(json, path, ...)`	指定されたパスでのキーと値を削除します。	`json_remove('[60,70,80,90]', '$[0]')`は`[70,80,90]`を返します。
`json_replace(json, path, value)`	指定されたパスに値を挿入します。既存の値を上書きしますが、存在しない場合は新しいキーを作成しません。
`json_set(json, path, value)`	指定されたパスに値を挿入します。既存の値を上書きします。
`json_type(json)` - `json_type(json, path)`	提供された値または指定されたパスでの値の型を返します。`null`、`true`、`false`、`integer`、`real`、`text`、`array`、または`object`のいずれかを返します。	`json_type('{"temperatures":[73.6, 77.8, 80.2]}', '$.temperatures')`は`array`を返します。
`json_valid(json)`	無効なJSONの場合は0（false）を、正しいJSONの場合は1（true）を返します。	`json_valid({invalid:json})`は`0`を返します。
`json_quote(value)`	提供されたSQL値をそのJSON表現に変換します。	`json_quote('[1, 2, 3]')`は`[1,2,3]`を返します。
`json_group_array(value)`	提供された値をJSON配列として返します。
`json_each(value)` - `json_each(value, path)`	オブジェクト内の各要素を個別の行として返します。トップレベルのオブジェクトのみをトラバースします。
`json_tree(value)` - `json_tree(value, path)`	オブジェクト内の各要素を個別の行として返します。オブジェクト全体をトラバースします。

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 (
    -- 他の列は省略
    raw_data TEXT -- JSON: {"measurement":{"aqi":[21,42,58],"wind_mph":"13","location":"US-NY"}}
    location AS (json_extract(raw_data, '$.measurement.location')) STORED
)

生成列の作成方法については、生成列を参照してください。

使用例

値の抽出

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

-- 温度値を抽出
json_extract(sensor_reading, '$.measurement.temp_f') -- "77.4"をTEXTとして返します

-- 最大PM2.5空気質測定値を抽出
sensor_reading -> '$.measurement.aqi[3]' -- 58をJSON数値として返します

-- o3（オゾン）配列を完全に抽出
sensor_reading -> '$.measurement.o3' -- '[18, 500]'をTEXTとして返します

配列の長さを取得

JSON配列の長さを取得する方法は2つあります：

json_array_length(value)を直接呼び出す
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') --> 3をINTEGERとして返します

json_array_lengthをより複雑なクエリの述語として使用することもできます - たとえば、WHERE json_array_length(some_column, '$.path.to.value') >= 5。

既存のオブジェクトに値を挿入

json_insert()を使用して、既存のJSONオブジェクトまたは配列に値を挿入できます。たとえば、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つの引数を提供します：

修正したいJSONを含む列の名前。
修正するオブジェクト内のキーへのパス。
挿入するJSON値。[#]を使用すると、json_insertは配列の末尾に追加することを指示します。

既存の値を置き換えるには、既存のキーと値のペアを上書きするjson_replace()を使用します。既存の有無にかかわらず値を設定するには、json_set()を使用します。

INクエリのための配列の展開

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]|$

json_eachをD1のclient APIと一緒にWorker内で使用するには、ステートメントを作成し、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()

これにより、idが提供された3つのいずれかと一致するusersテーブルの行のみが更新されます。

Cloudflare Dashboard Discord Community Learning Center Support Portal

Cookie Settings