Cordova-sqlite-storage: Is there a way to get all matched rows to one json array?

[TommyQu]

Such as PostgreSQL row_to_json function.

For example

tx.executeSql("select * from blog where created_by = ?;", ["admin"], function(tx, res) {
    var blogJson = JSON.stringify(all rows);
});

[brodybits]

Hi @TommyQu,

For review and possible benefit of others here is the most straightforward way:

tx.executeSql("select * from blog where created_by = ?;", ["admin"], function(tx, res) {
    var all_rows = [];
    for (i=0; i<res.rows.length; ++i)
        all_rows.push(res.rows.item(i);

    blogJson = JSON.stringify(all_rows);
});

I suspect it would be more efficient if you use json_ functions from the JSON1 extension. Unfortunately the JSON1 extension is not enabled in this version. The JSON1 extension functions are currently available in the Cordova-sqlcipher-adapter version. An idea under consideration is to update the cordova-sqlite-ext version with sqlite 3.15.2 and JSON1 enabled.

I tried the following in my sqlite 3.15.2 CLI tool:

sqlite> create table tt(a,b);
sqlite> insert into tt values(1,'aa');
sqlite> insert into tt values(2,'bb');
sqlite> select * from tt;
1|aa
2|bb
sqlite> select json(json_object('a',a,'b',b)) from tt;
{"a":1,"b":"aa"}
{"a":2,"b":"bb"}
sqlite> select "[" || group_concat(json_object('a',a,'b',b)) || "]" from tt;
[{"a":1,"b":"aa"},{"a":2,"b":"bb"}]

References:

• https://sqlite.org/json1.html
• http://sqlite.1065341.n5.nabble.com/Concatenate-single-field-from-multiple-records-td45426.html
• http://stackoverflow.com/questions/6134415/how-to-concatenate-strings-with-padding-in-sqlite

[TommyQu]

Thanks you very much Chris, it helps!

[payneBrandon]

I see you used the group_concat function in your test...is the group_concat functionality included with this version or is that only with the JSON1 extension in the Cordova-sqlcipher-adapter version?