AWS RDS Data API executeStatement not return column names

user_3524444 0
Viewed 7048

I'm playing with the New Data API for Amazon Aurora Serverless

Is it possible to get the table column names in the response?

If for example I run the following query in a user table with the columns id, first_name, last_name, email, phone:

const sqlStatement = `
    SELECT *
    FROM user
    WHERE id = :id 
`;
const params = {
    secretArn: <mySecretArn>,
    resourceArn: <myResourceArn>,
    database: <myDatabase>,
    sql: sqlStatement,
    parameters: [
        {
            name: "id",
            value: {
                "stringValue": 1
            }
        }
    ]
};
let res = await this.RDS.executeStatement(params)
console.log(res);

I'm getting a response like this one, So I need to guess which column corresponds with each value:

{
    "numberOfRecordsUpdated": 0,
    "records": [
        [
            {
                "longValue": 1
            },
            {
                "stringValue": "Nicolas"
            },
            {
                "stringValue": "Perez"
            },
            {
                "stringValue": "example@example.com"
            },
            {
                "isNull": true
            }
        ]
    ]
}

I would like to have a response like this one:

{
    id: 1,
    first_name: "Nicolas",
    last_name: "Perez",
    email: "example@example.com",
    phone: null
}

update1

I have found an npm module that wrap Aurora Serverless Data API and simplify the development

aws-rds-data-service amazon-aurora amazon-web-services
5 Answers
user_4140440

Agree with the consensus here that there should be an out of the box way to do this from the data service API. Because there is not, here's a JavaScript function that will parse the response.

const parseDataServiceResponse = res => {
    let columns = res.columnMetadata.map(c => c.name);
    let data = res.records.map(r => {
        let obj = {};
        r.map((v, i) => {
            obj[columns[i]] = Object.values(v)[0]
        });
        return obj
    })
    return data
}
user_101884170

I understand the pain but it looks like this is reasonable based on the fact that select statement can join multiple tables and duplicated column names may exist.

Similar to the answer above from @C.Slack but I used a combination of map and reduce to parse response from Aurora Postgres.

// declarative column names in array
const columns = ['a.id', 'u.id', 'u.username', 'g.id', 'g.name'];

// execute sql statement
const params = {
  database: AWS_PROVIDER_STAGE,
  resourceArn: AWS_DATABASE_CLUSTER,
  secretArn: AWS_SECRET_STORE_ARN,
  // includeResultMetadata: true,
  sql: `
    SELECT ${columns.join()} FROM accounts a 
    FULL OUTER JOIN users u ON u.id = a.user_id
    FULL OUTER JOIN groups g ON g.id = a.group_id
    WHERE u.username=:username;
  `,
  parameters: [
    {
      name: 'username',
      value: {
        stringValue: 'rick.cha',
      },
    },
  ],
};
const rds = new AWS.RDSDataService();
const response = await rds.executeStatement(params).promise();

// parse response into json array
const data = response.records.map((record) => {
  return record.reduce((prev, val, index) => {
    return { ...prev, [columns[index]]: Object.values(val)[0] };
  }, {});
});

Hope this code snippet helps someone.

And here is the response

[
  {
    'a.id': '8bfc547c-3c42-4203-aa2a-d0ee35996e60',
    'u.id': '01129aaf-736a-4e86-93a9-0ab3e08b3d11',
    'u.username': 'rick.cha',
    'g.id': 'ff6ebd78-a1cf-452c-91e0-ed5d0aaaa624',
    'g.name': 'valentree',
  },
  {
    'a.id': '983f2919-1b52-4544-9f58-c3de61925647',
    'u.id': '01129aaf-736a-4e86-93a9-0ab3e08b3d11',
    'u.username': 'rick.cha',
    'g.id': '2f1858b4-1468-447f-ba94-330de76de5d1',
    'g.name': 'ensightful',
  },
]
user_27548280

I've added to the great answer already provided by C. Slack to deal with AWS handling empty nullable character fields by giving the response { "isNull": true } in the JSON.

Here's my function to handle this by returning an empty string value - this is what I would expect anyway.

const parseRDSdata = (input) => {
let columns = input.columnMetadata.map(c => { return { name: c.name, typeName: c.typeName}; });

let parsedData = input.records.map(row => {
        let response = {};

        row.map((v, i) => {
                //test the typeName in the column metadata, and also the keyName in the values - we need to cater for a return value of { "isNull": true } - pflangan
                if ((columns[i].typeName == 'VARCHAR' || columns[i].typeName == 'CHAR') && Object.keys(v)[0] == 'isNull' && Object.values(v)[0] == true)
                   response[columns[i].name] = '';
                else
                  response[columns[i].name] = Object.values(v)[0];
            }
        );
        return response;
    }
);
return parsedData;

}

user_2470230

Similar to the other answers, but if you are using Python/Boto3:

def parse_data_service_response(res):
    columns = [column['name'] for column in res['columnMetadata']]

    parsed_records = []
    for record in res['records']:
        parsed_record = {}
        for i, cell in enumerate(record):
            key = columns[i]
            value = list(cell.values())[0]
            parsed_record[key] = value
        parsed_records.append(parsed_record)

    return parsed_records
user_65801420
Related