Using MySQL 8.0 on a node.js server (X DevAPI), I'm trying to get data with
var res = session.sql('SELECT * FROM users').execute(function (row) {
console.log(row); // [1,'foo','bar']
});
but this returns only an array of values. The column names are missing. Is there a way I can get key-value pairs such as that in classic SQL data sets?
i.e. id: 1, fName: 'foo', lName: 'bar'
Right now you would have to construct the key-value pair structure "by hand" using the column metadata available through a second callback parameter on execute().
Something like the following would do the trick:
var result = []
session.sql('SELECT 1 AS id, "foo" AS fName, "bar" AS lName')
.execute(row => {
row.forEach((value, i) => { result[i] = Object.assign({}, result[i], { value }) })
}, columns => {
columns.forEach((key, i) => { result[i] = Object.assign({}, result[i], { key: key.getColumnName() }) })
})
console.log(result.reduce((res, pair) => Object.assign(res, { [pair.key]: pair.value }), {}))
I have to admit this is convoluted and making it better is already on the radar.
Disclaimer: I'm the X DevAPI connector lead dev.
Use the JSON_OBJECT function in your query.
var res = session.sql("select JSON_OBJECT('id', id, 'name', name) from users").execute(function (row) {
console.log(row); // {"id": 87, "name": "rui"}
});
This is a workaround maybe will help someone.
//This is declared outside of class
const mapTest = (row) =>{
return{
id:row[0],
price:row[1]
}
}
//This where you get the result.
.execute()
.then(myResult =>{
let myRows = myResult.fetchAll();
resolve(myRows.map(row=>mapTest(row))); // returns {"id":1,"price":200000},{"id":2,"price":300000}
})
