Processar resultados de consulta da API de dados do Amazon RDS no formato JSON

Ao chamar a operação ExecuteStatement, você pode optar por ter os resultados da consulta retornados como uma string no formato JSON. Dessa forma, é possível usar os recursos de análise JSON da linguagem de programação para interpretar e reformatar o conjunto de resultados. Isso pode ajudar a evitar a necessidade de escrever código adicional para percorrer o conjunto de resultados e interpretar cada valor de coluna.

Para solicitar o conjunto de resultados no formato JSON, transmita o parâmetro formatRecordsAs opcional com um valor de JSON. O conjunto de resultados formatado em JSON é retornado no campo formattedRecords da estrutura ExecuteStatementResponse.

A ação BatchExecuteStatement não retorna um conjunto de resultados. Dessa forma, a opção JSON não se aplica a essa ação.

Para personalizar as chaves na estrutura de hash JSON, defina aliases de coluna no conjunto de resultados. Você pode fazer isso usando a cláusula AS na lista de colunas da consulta SQL.

É possível usar o recurso JSON para tornar o conjunto de resultados mais fácil de ler e mapear o respectivo conteúdo para frameworks específicos de linguagem. Como o volume do conjunto de resultados com codificação ASCII é maior do que a representação padrão, talvez você escolha a representação padrão para consultas que retornem números de linhas ou valores de coluna grandes que consomem mais memória do que o disponível para a aplicação.

Recuperar resultados de consulta no formato JSON

Para receber o conjunto de resultados como uma string JSON, inclua .withFormatRecordsAs(RecordsFormatType.JSON) na chamada ExecuteStatement. O valor de retorno volta como uma string JSON no campo formattedRecords. Nesse caso, columnMetadata é null. Os rótulos de coluna são as chaves do objeto que representa cada linha. Esses nomes de coluna são repetidos para cada linha do conjunto de resultados. Os valores de coluna são strings entre aspas, valores numéricos ou valores especiais que representam true, false ou null. Os metadados de coluna, como restrições de comprimento e o tipo preciso para números e strings, não são preservados na resposta JSON.

Se você omitir a chamada ao .withFormatRecordsAs() ou especificar um parâmetro de NONE, o conjunto de resultados será retornado em formato binário usando os campos Records e columnMetadata.

Mapeamento de tipo de dados

Os valores SQL no conjunto de resultados são mapeados para um conjunto menor de tipos JSON. Os valores são representados em JSON como strings, números e algumas constantes especiais, como true, false e null. Você pode converter esses valores em variáveis em sua aplicação, usando digitação forte ou fraca, conforme apropriado para sua linguagem de programação.

Solução de problemas

A resposta JSON é limitada a 10 megabytes. Se a resposta for maior que esse limite, seu programa receberá o erro BadRequestException. Nesse caso, você pode resolvê-lo com uma das seguintes técnicas:

Exemplos

Os exemplos de Java a seguir mostram como chamar o ExecuteStatement com a resposta como uma string formatada em JSON e como interpretar o conjunto de resultados. Substitua os valores apropriados para os parâmetros databaseName, secretStoreArn e clusterArn.

O exemplo Java a seguir demonstra uma consulta que retorna um valor numérico decimal no conjunto de resultados. As chamadas ao assertThat verificam se os campos da resposta têm as propriedades esperadas com base nas regras para conjuntos de resultados JSON.

Este exemplo funciona com o esquema e os dados de amostra a seguir:

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

O valor do campo formattedRecords do programa anterior é:

[{"a":10.0}]

Os campos Records e ColumnMetadata na resposta são nulos devido à presença do conjunto de resultados JSON.

O exemplo de Java a seguir demonstra uma consulta que retorna um valor numérico decimal no conjunto de resultados. No exemplo, o getFormattedRecords é chamado para retornar somente a string formatada em JSON e ignorar os outros campos de resposta que estão em branco ou são nulos. O exemplo desserializa o resultado em uma estrutura que representa uma lista de registros. Cada registro tem campos cujos nomes correspondem aos aliases de coluna do conjunto de resultados. Essa técnica simplifica o código que analisa o conjunto de resultados. Sua aplicação não precisa percorrer as linhas e colunas do conjunto de resultados e converter cada valor no tipo apropriado.

Este exemplo funciona com o esquema e os dados de amostra a seguir:

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

O valor do campo formattedRecords do programa anterior é:

[{"a":17}]

Para recuperar a coluna a da linha de resultado 0, a aplicação fará referência a recordsList.get(0).a.

Em contrapartida, o exemplo de Java a seguir mostra o tipo de código necessário para criar uma estrutura de dados que contenha o conjunto de resultados quando o formato JSON não é usado. Nesse caso, cada linha do conjunto de resultados contém campos com informações sobre um único usuário. Para criar uma estrutura de dados a fim de representar o conjunto de resultados, é necessário examinar as linhas. Para cada linha, o código recupera o valor de cada campo, realiza uma conversão de tipo apropriada e atribui o resultado ao campo correspondente no objeto que representa a linha. Depois, o código adiciona o objeto que representa cada usuário à estrutura de dados que representa todo o conjunto de resultados. Se a consulta tiver sido alterada para reordenar, adicionar ou remover campos no conjunto de resultados, o código da aplicação também precisará mudar.

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

Os valores de exemplo a seguir mostram os valores do campo formattedRecords para conjuntos de resultados com diferentes números e aliases de coluna, bem como tipos de dados de coluna.

Se o conjunto de resultados incluir várias linhas, cada uma será representada como um objeto que é um elemento de matriz. Cada coluna no conjunto de resultados se torna uma chave no objeto. Esses nomes de coluna são repetidos para cada linha do conjunto de resultados. Dessa forma, para conjuntos de resultados que compreendem muitas linhas e colunas, talvez seja necessário definir aliases de coluna curtos para não exceder o limite de comprimento da resposta completa.

Este exemplo funciona com o esquema e os dados de amostra a seguir:

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

Se uma coluna no conjunto de resultados for definida como uma expressão, o texto da expressão se tornará a chave JSON. Por isso, em geral é conveniente definir um alias de coluna descritivo para cada expressão na lista de seleção da consulta. Por exemplo, a consulta a seguir inclui expressões como chamadas de função e operações aritméticas em sua lista de seleção.

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

Essas expressões são transmitidas para o conjunto de resultados JSON como chaves.

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

Adicionar colunas AS com rótulos descritivos torna as chaves mais simples de interpretar no conjunto de resultados JSON.

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

Com a consulta SQL revisada, os rótulos de coluna definidos pelas cláusulas AS são usados como nomes de chave.

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

O valor para cada par de chave-valor na string JSON pode ser uma string entre aspas. A string pode conter caracteres unicode. Se a string contiver sequências de escape ou os caracteres " ou \, esses caracteres serão precedidos por caracteres de escape de barra invertida. Os exemplos de strings JSON a seguir demonstram essas possibilidades. Por exemplo, o resultado string_with_escape_sequences contém os caracteres especiais backspace, nova linha, retorno de carro, guia, feed de formulário e \.

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

O valor para cada par de chave-valor na string JSON pode ser uma string entre aspas. O número pode ser um valor inteiro, um valor de ponto flutuante, um valor negativo ou um valor representado como notação exponencial. Os exemplos de strings JSON a seguir demonstram essas possibilidades.

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

Valores boolianos e nulos são representados com as palavras-chave especiais true, false e null sem aspas. Os exemplos de strings JSON a seguir demonstram essas possibilidades.

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

Se você selecionar um valor de um tipo BLOB, o resultado será representado na string JSON como um valor codificado em base64. Para converter o valor de volta à representação original, você pode usar a função de decodificação apropriada na linguagem da aplicação. Por exemplo, em Java, chame a função Base64.getDecoder().decode(). O exemplo de saída a seguir mostra o resultado da seleção de um valor BLOB de hello world e do retorno do conjunto de resultados como uma string JSON.

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

O exemplo de Python a seguir mostra como acessar os valores do resultado de uma chamada para a função execute_statement do Python. O conjunto de resultados é um valor de string no campo response['formattedRecords']. O código transforma a string JSON em uma estrutura de dados chamando a função json.loads. Depois, cada linha do conjunto de resultados é um elemento de lista dentro da estrutura de dados e, em cada linha, é possível fazer referência a cada campo do conjunto de resultados pelo nome.

import json

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

O exemplo de Javascript a seguir mostra como acessar os valores do resultado de uma chamada para a função executeStatement do Javascript. O conjunto de resultados é um valor de string no campo response.formattedRecords. O código transforma a string JSON em uma estrutura de dados chamando a função JSON.parse. Depois, cada linha do conjunto de resultados é um elemento de matriz dentro da estrutura de dados e, em cada linha, é possível fazer referência a cada campo do conjunto de resultados pelo nome.

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