Get results in wide format

[jeroen]

Is there an easy way to call db.exec and get the results in the conventional format:

[
  { "person" : "jerry", "age" : 34 },
  { "person" : "mary", "age": 37 },
  { "person" : "joe", "age" : 63 }
]

I am currently using:

var out = []
var res = db.prepare("SELECT * FROM hello");
while (stmt.step()) out.push(stmt.getAsObject());

But push() can be inefficient. It would be nice to have a shorthand for this.

[lovasoa]

There is no 'conventional format'...

I agree that it looks more logical, and easier to manipulate.

But it also has inconvenients. Loading all the results in one array is rarely necessary... Why not just iterating over the prepared statement? Putting the rows of results in objects makes it impossible to access the fields in the order specified by the query, hides the columns with duplicate names, and encourages you to create a new string every time you want to access a field...

But I understand that performance is not always important (especially when using sql.js), and that new users might just want to get all the rows of result at once in a familiar format.

Creating a db.queryObjects (or whatever we will call it) is not excluded.

[williamd1k0]

Hello, I started using SQLite recently and met this tool (sql.js) today, for my purpose would be better a select that returns an array of objects as shown above, and I wrote this function just now to test, I just do not know if it's the best way of doing this.

Anyway I'll let the code here where helpful. And thanks for the great job in this tool.

_db is the instance of SQL.Database and the function name is temporary.

function select(query){
        query = _db.exec(query);
        query = query[0];

        var queryObjects = [];
        var keys = query.columns;
        var values = query.values;

        for(var i = 0; i < values.length; i++){
            var valueObject = {};
            for(var j = 0; j < keys.length; j++){
                valueObject[keys[j]] = values[i][j];
            }
            queryObjects.push(valueObject);
        }
        return queryObjects;
    }

[chaserstrong]

@williamd1k0 thank you very much for sharing this function. it's helpful!

[pdanielkoe]

share my approach incase somebody need it too,

db.exec('SELECT * FROM items; SELECT * FROM items WHERE id = "version";').map(
    ({ columns, values }) => {
        return values.map(
            (item) => {
                return Object.assign(
                    {}, ...item.map(
                        (el, index) => {
                            return {
                                [columns[index]]: el
                            };
                        }
                    )
                );
            }
        );
    }
)

OR

db.exec('SELECT * FROM items; SELECT * FROM items WHERE id = "version";').map(
    ({ columns, values }) => values.map((item) =>
        Object.assign(
            {}, ...item.map(
                (el, index) => {
                    return { [columns[index]]: el }
                }
            )
        )
    )
)

OR

db.exec('SELECT * FROM items; SELECT * FROM items WHERE id = "version";').map(({ columns, values }) => values.map((x) => Object.assign({}, ...x.map((v, i) => ({ [columns[i]]: v })))))

Before: After: