JSON 形式で RDS Data API クエリ結果を処理する

ExecuteStatement オペレーションを呼び出す場合、クエリ結果を JSON 形式の文字列として返すように選択できます。これにより、プログラミング言語の JSON 解析機能を使用して結果セットを解釈し、再フォーマットできます。そうすることで、結果セットをループして各列の値を解釈するために、余分なコードを記述せずに済むようになります。

JSON 形式で結果セットをリクエストするには、オプションの formatRecordsAs パラメータに JSON の値を渡します。JSON 形式の結果セットは、ExecuteStatementResponse 構造の formattedRecords に返されます。

BatchExecuteStatement アクションは結果セットを返しません。したがって、JSON オプションはそのアクションには適用されません。

JSON ハッシュ構造のキーをカスタマイズするには、結果セットにカラムのエイリアスを定義します。SQL クエリの列リスト内で AS 句を指定することで定義できます。

JSON 機能を使用することで、結果セットが読みやすくなり、その内容を言語固有のフレームワークにマッピングしやすくなります。ASCII でエンコードされた結果セットのボリュームはデフォルトの表記よりも大きいため、大きな行または列値を返すクエリではアプリケーションで使用可能なメモリよりも多くのメモリを消費することになり、この場合はデフォルトの表記を選択する方が良いでしょう。

JSON 形式でクエリ結果を取得する

結果セットを JSON 文字列として受け取るには、ExecuteStatement の呼び出しに .withFormatRecordsAs(RecordsFormatType.JSON) を含めます。戻り値は、formattedRecords フィールドに JSON 文字列として戻ります。この場合、columnMetadata は null です。列ラベルは、各行を表すオブジェクトのキーです。これらの列名は、結果セットの行ごとに繰り返されます。列値は引用符で囲まれた文字列、数値、または true、false、null を表す特殊な値です。長さの制約、数値、文字列の正確な型などの列のメタデータは、JSON レスポンスでは保持されません。

.withFormatRecordsAs() 呼び出しを省略、または NONE のパラメータを指定した場合、結果セットは Records と columnMetadata のフィールドを使用してバイナリ形式で返されます。

データ型のマッピング

結果セットの SQL 値は、より小さい JSON 型のセットにマッピングされます。値は JSON で文字列、数値、および true、false、null などの特殊な定数で表現されます。これらの値は、プログラミング言語に適した強い型付けまたは弱い型付けを使用して、アプリケーション内の変数に変換できます。

トラブルシューティング

JSON レスポンスは 10 メガバイトに制限されています。レスポンスがこの制限よりも大きい場合、プログラムには BadRequestException エラーが返されます。この場合、以下のいずれかの方法でエラーを解決できます。

例

以下の Java の例では、レスポンスを JSON 形式の文字列として ExecuteStatement を呼び出し、結果セットを解釈する方法を示しています。databaseName、secretStoreArn、clusterArn パラメータを適切な値に置き換えます。

以下の Java の例では、結果セットに 10 進数の値を返すクエリを示しています。assertThat 呼び出しは、JSON 結果セットのルールに基づいて、レスポンスのフィールドが、期待されるプロパティを持っているかをテストします。

この例では、以下のスキーマとサンプルデータで動作します。

create table test_simplified_json (a float);
insert into test_simplified_json values(10.0);

public void JSON_result_set_demo() {
    var sql = "select * from test_simplified_json";
    var request = new ExecuteStatementRequest()
      .withDatabase(databaseName)
      .withSecretArn(secretStoreArn)
      .withResourceArn(clusterArn)
      .withSql(sql)
      .withFormatRecordsAs(RecordsFormatType.JSON);
    var result = rdsdataClient.executeStatement(request);
}

前のプログラムの formattedRecords フィールドの値は以下のとおりです。

[{"a":10.0}]

JSON 結果セットが存在するため、レスポンスの Records と ColumnMetadata フィールドは両方とも null になります。

以下の Java の例では、結果セットに整数の値を返すクエリを示しています。この例では getFormattedRecords を呼び出します。JSON 形式の文字列のみを返し、他のレスポンスフィールドが空白または null の場合は無視します。この例では、結果をレコードのリストを表す構造体に逆シリアル化しています。各レコードには、結果セットの列のエイリアスに対応する名前を持つフィールドがあります。この方法では、結果セットを解析するコードを単純化します。アプリケーションでは、結果セットの行と列をループ処理して、各値を適切な型に変換する必要がありません。

この例では、以下のスキーマとサンプルデータで動作します。

create table test_simplified_json (a int);
insert into test_simplified_json values(17);

public void JSON_deserialization_demo() {
    var sql = "select * from test_simplified_json";
    var request = new ExecuteStatementRequest()
      .withDatabase(databaseName)
      .withSecretArn(secretStoreArn)
      .withResourceArn(clusterArn)
      .withSql(sql)
      .withFormatRecordsAs(RecordsFormatType.JSON);
    var result = rdsdataClient.executeStatement(request)
      .getFormattedRecords();

/* Turn the result set into a Java object, a list of records.
   Each record has a field 'a' corresponding to the column
   labelled 'a' in the result set. */
    private static class Record { public int a; }
    var recordsList = new ObjectMapper().readValue(
        response, new TypeReference<List<Record>>() {
        });
}

前のプログラムの formattedRecords フィールドの値は以下のとおりです。

[{"a":17}]

結果行 0 の a 列を取得するには、アプリケーションは recordsList.get(0).a を参照します。

一方で、以下の Java の例は、JSON 形式を使用しない場合に、結果セットを保持するデータ構造を構築するために必要なコードの種類を示しています。この場合、結果セットの各行には 1 人のユーザーに関する情報を含むフィールドが含まれています。結果セットを表すデータ構造を構築するには、行をループする必要があります。各行について、コードによって各フィールドの値を取得して、適切な型変換を実行し、その行を表すオブジェクト内の対応するフィールドに結果を代入します。次に、各ユーザーを表すオブジェクトを、結果セット全体を表すデータ構造に追加します。クエリの結果セット内でフィールドの並べ替え、追加、削除などで変更があった場合、アプリケーションコードも変更する必要があります。

/* Verbose result-parsing code that doesn't use the JSON result set format */
for (var row: response.getRecords()) {
    var user = User.builder()
      .userId(row.get(0).getLongValue())
      .firstName(row.get(1).getStringValue())
      .lastName(row.get(2).getStringValue())
      .dob(Instant.parse(row.get(3).getStringValue()))
      .build();
    result.add(user);
  } 

以下のサンプル値は、列数、列のエイリアス、列のデータ型が異なる結果セットの formattedRecords フィールドの値を示しています。

結果セットに複数の行が含まれている場合、各行は配列要素であるオブジェクトとして表されます。結果セット内の各列は、オブジェクトのキーになります。このキーは、結果セットの行ごとに繰り返されます。このため、多くの行と列で構成される結果セットでは、レスポンス全体の長さ制限を超えないように、短い列のエイリアスを定義する必要があります。

この例では、以下のスキーマとサンプルデータで動作します。

create table sample_names (id int, name varchar(128));
insert into sample_names values (0, "Jane"), (1, "Mohan"), (2, "Maria"), (3, "Bruce"), (4, "Jasmine");

[{"id":0,"name":"Jane"},{"id":1,"name":"Mohan"},
{"id":2,"name":"Maria"},{"id":3,"name":"Bruce"},{"id":4,"name":"Jasmine"}]

結果セットの列が式として定義されている場合、式のテキストが JSON キーになります。したがって、通常、クエリの選択リスト内の式ごとに説明的な列のエイリアスを定義すると便利です。例えば、以下のクエリでは、関数呼び出しや算術演算などの式が選択リストに含まれています。

select count(*), max(id), 4+7 from sample_names;

これらの式は JSON 結果セットにキーとして渡されます。

[{"count(*)":5,"max(id)":4,"4+7":11}]

AS 列に説明的なラベルを追加することで、JSON 結果セットでキーが簡単に解釈できます。

select count(*) as rows, max(id) as largest_id, 4+7 as addition_result from sample_names;

変更された SQL クエリでは、AS 句で定義した列のラベルがキー名として使用されます。

[{"rows":5,"largest_id":4,"addition_result":11}]

JSON 文字列の各キーバリューペアの値は、引用符で囲まれた文字列にできます。文字列には Unicode 文字が含まれている場合があります。文字列にエスケープシーケンスが含まれている場合、または " や \ 文字の場合、それらの文字の前にバックスラッシュ (エスケープ) 文字が付きます。以下に、可能性のある JSON 文字列の例を示します。例えば、string_with_escape_sequences の結果には、特殊文字バックスペース、改行、キャリッジリターン、タブ、フォームフィード、\ などがあります。

[{"quoted_string":"hello"}]
[{"unicode_string":"邓不利多"}]
[{"string_with_escape_sequences":"\b \n \r \t \f \\ '"}] 

JSON 文字列の各キーバリューペアの値は、数値で表すことができます。数値は、整数、浮動小数点数、負の値、指数表記などで表される値になる場合があります。以下に、可能性のある JSON 文字列の例を示します。

[{"integer_value":17}]
[{"float_value":10.0}]
[{"negative_value":-9223372036854775808,"positive_value":9223372036854775807}]
[{"very_small_floating_point_value":4.9E-324,"very_large_floating_point_value":1.7976931348623157E308}] 

ブール値と NULL 値は、引用符で囲まれていない特殊キーワード true、false、null で表されます。以下に、可能性のある JSON 文字列の例を示します。

[{"boolean_value_1":true,"boolean_value_2":false}]
[{"unknown_value":null}] 

BLOB タイプの値を選択した場合、結果は base64 でエンコードされた値として JSON 文字列で表されます。値を元の表現に戻すには、アプリケーションの言語で適切なデコード関数を使用できます。例えば、Java では、Base64.getDecoder().decode() 関数を呼び出します。以下の出力例は、hello world の BLOB 値を選択し、結果セットを JSON 文字列として返した結果を示しています。

[{"blob_column":"aGVsbG8gd29ybGQ="}]

以下の Python の例は、Python の execute_statement 関数の呼び出し結果から、値にアクセスする方法を示しています。結果セットは、response['formattedRecords'] フィールド内の文字列の値です。このコードでは、json.loads 関数を呼び出すことで、JSON 文字列をデータ構造に変換します。また、結果セットの各行はデータ構造内のリスト要素であり、各行内では、結果セットの各フィールドを名前で参照できます。

import json

result = json.loads(response['formattedRecords'])
print (result[0]["id"])

以下の JavaScript の例は、JavaScript の executeStatement 関数の呼び出し結果から、値にアクセスする方法を示しています。結果セットは、response.formattedRecords フィールド内の文字列の値です。このコードでは、JSON.parse 関数を呼び出すことで、JSON 文字列をデータ構造に変換します。また、結果セットの各行はデータ構造内の配列要素であり、各行内では、結果セットの各フィールドを名前で参照できます。

<script>
    const result = JSON.parse(response.formattedRecords);
    document.getElementById("display").innerHTML = result[0].id;
</script>