JSON 関数を使用すると、テーブルの列に JSON 形式で格納されているデータをクエリできます。Tablestore SQL でサポートされている JSON 関数は、MySQL 5.7 の構文を使用します。このトピックでは、Tablestore SQL でサポートされている JSON 関数と、それらの使用方法について説明します。
サポートされている JSON 関数
次の表に、Tablestore SQL でサポートされている JSON 関数を示します。
JSON 関数 | 説明 |
JSON ドキュメントから、指定したパスに基づいてデータを取得し、結果をクォート解除して文字列に変換した後に返します。この関数は、 | |
JSON 値のクォートを解除し、結果を文字列として返します。 | |
JSON ドキュメントからデータを返します。返されるデータは、引数として指定したパスに基づいて特定されます。 |
->>
->> 関数は、JSON ドキュメントから、指定したパスに基づいてデータを取得し、結果をクォート解除して文字列に変換した後に返します。この関数は、JSON_UNQUOTE(JSON_EXTRACT()) と同等です。
構文
column->>path説明
戻り値は、指定した path パラメーターに基づいて特定された値です。
パラメーターの値が NULL であるか、path パラメーターで指定されたパスがドキュメントに見つからない場合は、NULL が返されます。
パラメーター
パラメーター | タイプ | 説明 |
column | 文字列 | 列の名前。 |
path | 文字列 | JSON ドキュメントのパス。 path パラメーターの値は、JSON ドキュメント全体を指定する |
例
次の SQL ステートメントを実行すると、json_table テーブルの pkint 主キー列の値が 1 である行の coljson 列から、$.a パスで特定されるデータをクエリできます。
SELECT coljson, coljson->>'$.a' AS subdoc FROM json_table WHERE pkint = 1;次の図は、出力例を示しています。
JSON_UNQUOTE
JSON_UNQUOTE 関数は、JSON 値のクォートを解除し、結果を文字列として返します。
構文
JSON_UNQUOTE(json_val)説明
戻り値は、JSON 値のクォートを解除して文字列に変換した後に取得される値です。
json_val パラメーターの値が NULL の場合、NULL が返されます。
JSON 値が二重引用符 (") で始まり、二重引用符で終わるものの、有効な JSON 文字列ではない場合、エラーが発生します。
パラメーター
パラメーター | 説明 |
json_val | JSON_EXTRACT ステートメント。詳細については、JSON_EXTRACT を参照してください。 |
例
次の SQL ステートメントを実行すると、json_table テーブルの pkint 主キー列の値が 1 である行の coljson 列から、$.a パスで特定されるデータをクエリできます。
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;次の図は、出力例を示しています。
JSON_EXTRACT
JSON_EXTRACT 関数は JSON ドキュメントからデータを返します。返されるデータは、引数として指定したパスに基づいて特定されます。Tablestore はネイティブの JSON データ型をサポートしていません。JSON データ型を使用すると、無効なデータ型を示すエラーが返されます。そのため、JSON_EXTRACT を JSON_UNQUOTE と一緒に使用する必要があります。
構文
JSON_EXTRACT(json_doc, path[, path] ...)説明
戻り値は、path パラメーターで指定されたパスに基づいて特定されたすべての値で構成されます。path パラメーターで指定されたパスに基づいて複数の値が特定された場合、値は path パラメーターでパスが指定された順序に基づいて自動的に配列にラップされます。path パラメーターで指定されたパスに基づいて 1 つの値のみが特定された場合は、その値が返されます。
パラメーターの値が NULL であるか、path パラメーターで指定されたパスがドキュメントに見つからない場合は、NULL が返されます。
パラメーター
パラメーター | タイプ | 説明 |
json_doc | 文字列 | JSON ドキュメント。 重要 json_doc パラメーターで指定された JSON ドキュメントが無効であるか、path パラメーターで指定されたパス式が無効な場合、エラーが発生します。 |
path | 文字列 | JSON ドキュメントのパス。 path パラメーターの値は、JSON ドキュメント全体を指定する |
例
次の SQL ステートメントを実行すると、json_table テーブルの pkint 主キー列の値が 1 である行の coljson 列から、$.a パスで特定されるデータをクエリできます。この例では、coljson 列の値は {"a": 1, "b":2, "c":{"d":4}} です。
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a')) AS subdoc FROM json_table WHERE pkint = 1;次の図は、出力例を示しています。
複数のパスを同時に指定することもできます。戻り値は配列形式です。たとえば、次の SQL ステートメントを実行すると、json_table テーブルの pkint 主キー列の値が 1 である行の coljson 列から、$.a、$.b、$.c.d パスに基づいて特定されるデータをクエリできます。この例では、coljson 列の値は {"a": 1, "b":2, "c":{"d":4}} です。
SELECT coljson, JSON_UNQUOTE(JSON_EXTRACT(coljson, '$.a', '$.b', '$.c.d')) AS subdoc FROM json_table WHERE pkint = 1;次の図は、出力例を示しています。
JSON パス
path パラメーターは、JSON ドキュメントのパスを特定するために使用されます。
path パラメーターの値は、JSON ドキュメント全体を指定する $ で始まる必要があります。 path パラメーターの値には、1 つ以上のパスセレクターを指定できます。
JSON ドキュメント内のパスに基づいてデータが特定されない場合は、NULL が返されます。
パスセレクター
以下は、一般的なパスセレクターです。
$.keyは JSON オブジェクトを選択するために使用されます。ピリオド (.) の後にキーを追加して、キーに対応するオブジェクトを選択できます (例:$.a)。指定したキーにスペースが含まれている場合は、キーを二重引用符 (") で囲みます (例:$."a b")。[N]は、JSON 配列の添字を選択するために使用されます。添字は 0 から始まります。例:$[0]、$[1]。パスには、アスタリスク (*) と
**ワイルドカード文字を含めることができます。以下は、アスタリスク (*) と ** ワイルドカード文字を含むパスの使用方法です。.*: JSON オブジェクト内のすべてのメンバーの値を計算するために使用されます。[*]: JSON 配列内のすべての要素の値を計算するために使用されます。prefix**suffix: 特定のプレフィックスで始まり、特定のサフィックスで終わるすべてのパスを指定するために使用されます。
例
JSON オブジェクトのクエリ
たとえば、次の JSON オブジェクトをクエリするとします。
{"a": 1, "f": [1, 2, 3], "c": {"d": 4}}次の表に、異なるパスセレクターが構成された場合の戻り値を示します。
パスセレクター | 戻り値 |
$ |
|
$.a | 1 |
$.c |
|
$.c.d | 4 |
$.f[1] | 2 |
たとえば、キーにスペースが含まれている次の JSON オブジェクトをクエリするとします。
{"a fish": "shark", "a bird": "sparrow"}次の表に、異なるパスセレクターが構成された場合の戻り値を示します。
パスセレクター | 戻り値 |
$."a fish" | shark |
$."a bird" | sparrow |
JSON 配列のクエリ
たとえば、次の JSON 配列をクエリするとします。
[3, {"a": [5, 6], "b": 10}, [99, 100]]次の表に、異なるパスセレクターが構成された場合の戻り値を示します。
戻り値がスカラー値でない場合は、ネストされたクエリを実行できます。たとえば、$[1] と $[2] に対して返される値はスカラー値ではありません。この場合、$[1].a と $[2][0] を使用してネストされたクエリを実行できます。
パスセレクター | 戻り値 |
$[0] | 3 |
$[1] |
|
$[1].a |
|
$[1].a[1] | 6 |
$[1].b | 10 |
$[2] |
|
$[2][0] | 99 |
$[3] | NULL |
ワイルドカードクエリの実行
たとえば、次の JSON オブジェクトをクエリするとします。
{"a": 1, "b": 2, "c": [3, 4, 5]}次の表に、異なるパスセレクターが構成された場合の戻り値を示します。
パスセレクター | 戻り値 |
$.* |
|
$.c[*] |
|
たとえば、次の JSON オブジェクトをクエリするとします。
{"a": {"b": 1}, "c": {"b": 2}}パスセレクターを $**.b に設定した場合 (この例では $.a.b と $.c.b と同等)、戻り値は [1, 2] です。