Procesamiento de resultados de consultas de API de datos de Amazon RDS en formato JSON

Cuando se llama a la operación ExecuteStatement, se puede elegir que los resultados de la consulta se devuelvan como cadena en formato JSON. Esto permite utilizar las capacidades de análisis JSON de su lenguaje de programación para interpretar el conjunto de resultados y volver a darle formato. Hacerlo puede ayudar a evitar escribir código adicional para pasar por el conjunto de resultados e interpretar el valor de cada columna.

Para solicitar el conjunto de resultados en formato JSON, es preciso pasar el parámetro opcional formatRecordsAs con un valor JSON. El conjunto de resultados con formato JSON se devuelve en el campo formattedRecords de la estructura ExecuteStatementResponse.

La acción BatchExecuteStatement no devuelve un conjunto de resultados. Por lo tanto, la opción JSON no se aplica a esa acción.

Para personalizar las claves de la estructura hash JSON, defina alias de columna en el conjunto de resultados. Puede hacerlo mediante la cláusula AS de la lista de columnas de la consulta SQL.

Puede hacer uso de la capacidad JSON para facilitar la lectura del conjunto de resultados y asignar su contenido a marcos específicos de lenguaje. Dado que el volumen del conjunto de resultados codificado en ASCII es mayor que la representación predeterminada, puede elegir la representación predeterminada para consultas que devuelvan un gran número de filas o valores de columna grandes que consuman más memoria de la que está disponible para la aplicación.

Recuperación de resultados de consultas en formato JSON

Para recibir el conjunto de resultados como una cadena JSON, incluya .withFormatRecordsAs(RecordsFormatType.JSON) en la llamada a ExecuteStatement. El valor devuelto vuelve como cadena JSON en el campo formattedRecords. En este caso, columnMetadata es null. Las etiquetas de columna son las claves del objeto que representa cada fila. Estos nombres de columna se repiten para cada fila del conjunto de resultados. Los valores de columna son cadenas entre comillas, valores numéricos o valores especiales que representan true, false o null. Los metadatos de columna, como las restricciones de longitud y el tipo preciso de números y cadenas, no se conservan en la respuesta JSON.

Si omite la llamada .withFormatRecordsAs() o especifica un parámetro de NONE, el conjunto de resultados se devuelve en formato binario mediante los campos Records y columnMetadata.

Asignación de tipos de datos

Los valores SQL del conjunto de resultados se asignan a un conjunto más pequeño de tipos JSON. Los valores se representan en JSON como cadenas, números y ciertas constantes especiales, como true, false y null. Puede convertir estos valores en variables en su aplicación mediante tipado fuerte o débil según corresponda en el lenguaje de programación.

Solución de problemas

La respuesta JSON se limita a 10 megabytes. Si la respuesta supera este límite, el programa recibe un error BadRequestException. En este caso, puede resolver el error mediante una de las siguientes técnicas:

Ejemplos

Los siguientes ejemplos de Java muestran cómo llamar a ExecuteStatement con la respuesta como cadena con formato JSON y, a continuación, interpretar el conjunto de resultados. Sustituya los valores apropiados por los parámetros databaseName, secretStoreArn y clústerArn.

En el siguiente ejemplo de Java se muestra una consulta que devuelve un valor numérico decimal en el conjunto de resultados. Las llamadas assertThat prueban que los campos de la respuesta tengan las propiedades esperadas según las reglas de los conjuntos de resultados JSON.

Este ejemplo funciona con el siguiente esquema y datos de ejemplo:

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);
}

El valor del campo formattedRecords del programa anterior es:

[{"a":10.0}]

Los campos Records y ColumnMetadata de la respuesta son nulos debido a la presencia del conjunto de resultados JSON.

En el siguiente ejemplo de Java se muestra una consulta que devuelve un valor numérico entero en el conjunto de resultados. En el ejemplo se llama a getFormattedRecords para devolver solo la cadena con formato JSON e ignorar los demás campos de respuesta en blanco o nulos. En el ejemplo se deserializa el resultado en una estructura que representa una lista de registros. Cada registro tiene campos cuyos nombres corresponden a los alias de columna del conjunto de resultados. Esta técnica simplifica el código que analiza el conjunto de resultados. La aplicación no tiene que recorrer las filas y las columnas del conjunto de resultados y convertir cada valor al tipo adecuado.

Este ejemplo funciona con el siguiente esquema y datos de ejemplo:

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>>() {
        });
}

El valor del campo formattedRecords del programa anterior es:

[{"a":17}]

Para recuperar la columna a de la fila de resultados 0, la aplicación haría referencia a recordsList.get(0).a.

En cambio, en el siguiente ejemplo de Java se muestra el tipo de código necesario para construir una estructura de datos que contenga el conjunto de resultados cuando no se utilice el formato JSON. En este caso, cada fila del conjunto de resultados contiene campos con información sobre un solo usuario. La creación de una estructura de datos para representar el conjunto de resultados requiere recorrer las filas en bucle. Para cada fila, el código recupera el valor de cada campo, hace la conversión de tipo adecuada y asigna el resultado al campo correspondiente del objeto que representa la fila. A continuación, el código añade el objeto que representa a cada usuario a la estructura de datos que representa todo el conjunto de resultados. Si la consulta se modificó para reordenar, agregar o quitar campos del conjunto de resultados, el código de la aplicación tendría que cambiar también.

/* 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);
  } 

Los siguientes valores de ejemplo muestran los valores del campo formattedRecords para conjuntos de resultados con diferentes números de columnas, alias de columna y tipos de datos de columnas.

Si el conjunto de resultados incluye varias filas, cada fila se representa como un objeto que es un elemento de matriz. Cada columna del conjunto de resultados se convierte en una clave dentro del objeto. Las claves se repiten para cada fila del conjunto de resultados. Por lo tanto, para los conjuntos de resultados que constan de varias filas y columnas, es posible que deba definir alias de columna cortos para evitar superar el límite de longitud de toda la respuesta.

Este ejemplo funciona con el siguiente esquema y datos de ejemplo:

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

Si una columna del conjunto de resultados se define como expresión, el texto de la expresión se convierte en la clave JSON. Así pues, suele ser conveniente definir un alias de columna descriptivo para cada expresión de la lista de selección de la consulta. Por ejemplo, la siguiente consulta incluye expresiones como llamadas a funciones y operaciones aritméticas en su lista de selección.

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

Esas expresiones se pasan al conjunto de resultados JSON como claves.

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

Añadir columnas AS con etiquetas descriptivas simplifica la interpretación de las claves en el conjunto de resultados JSON.

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

Con la consulta SQL revisada, se utilizan como nombres de clave las etiquetas de columna definidas por las cláusulas AS.

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

El valor de cada par clave-valor de la cadena JSON puede ser una cadena entre comillas. La cadena podría contener caracteres unicode. Si la cadena contiene secuencias de escape o caracteres " o \, esos personajes van precedidos de caracteres de escape de barra invertida. Los siguientes ejemplos de cadenas JSON muestran estas posibilidades. Por ejemplo, el resultado string_with_escape_sequences contiene el valor de retroceso para caracteres especiales, nueva línea, retorno de carro, sangría, salto de página y \.

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

El valor de cada par clave-valor de la cadena JSON puede también representar un número. El número puede ser un entero, un valor de coma flotante, un valor negativo o un valor representado como notación exponencial. Los siguientes ejemplos de cadenas JSON muestran estas posibilidades.

[{"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}] 

Los valores booleanos y nulos se representan con las palabras clave especiales sin comillas true, false y null. Los siguientes ejemplos de cadenas JSON muestran estas posibilidades.

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

Si selecciona un valor de tipo BLOB, el resultado se representa en la cadena JSON como un valor codificado en base64. Para convertir el valor a su representación original, puede utilizar la función de descodificación adecuada en el lenguaje de la aplicación. Por ejemplo, en Java se llama a la función Base64.getDecoder().decode(). El siguiente ejemplo muestra el resultado de seleccionar un valor BLOB de hello world y devuelve el conjunto de resultados como una cadena JSON.

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

El siguiente ejemplo de Python muestra cómo acceder a los valores del resultado de una llamada a la función de Python execute_statement. El conjunto de resultados es un valor de cadena en el campo response['formattedRecords']. El código convierte la cadena JSON en una estructura de datos llamando a la función json.loads. A continuación, cada fila del conjunto de resultados es un elemento de lista dentro de la estructura de datos y, dentro de cada fila, puede hacer referencia a cada campo del conjunto de resultados por nombre.

import json

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

En el siguiente ejemplo de JavaScript se muestra cómo acceder a los valores del resultado de una llamada a la función executeStatement de JavaScript. El conjunto de resultados es un valor de cadena en el campo response.formattedRecords. El código convierte la cadena JSON en una estructura de datos llamando a la función JSON.parse. A continuación, cada fila del conjunto de resultados es un elemento de matriz dentro de la estructura de datos y, dentro de cada fila, puede hacer referencia a cada campo del conjunto de resultados por nombre.

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