Get JSON representation of result rows

[jamesaoverton]

In Nanobot we're using Vec<Map<String, Value>> (where Map and Value are from serde_json) to represent SQL results. Since we're using JSON throughout Nanobot, this is convenient.

The current Nanobot implementation is SQLite-specific, and uses SQLite's json_object() in the SQL query to ask SQLite to return JSON strings, then serde_json::from_str to read them.

I would like some options in SQLREST to return Vec<Map<String, Value>>. I'm not sure whether the json_object() approach is best, and I'm open to better suggestions, but it does work.

[lmcmicu]

sqlx has a query_as! macro that can be used to convert the results of a query to json, but unfortunately it seems to require that the row fields be defined at compile time (see here: launchbadge/sqlx#795 (comment)) so this won't work for us.

Another option is to look up the column info for the given row using the columns() and type_info() methods and then using that info to construct a Value, i.e., something like this:

let query = sqlx_query(r#"SELECT "col1", "col2", "col3" FROM "test""#);
let rows = block_on(query.fetch_all(pool)).unwrap();
let mut json_rows = vec![];
for row in rows.iter() {
    let mut json_row = serde_json::Map::new();
    for column in row.columns().iter() {
        let cname = column.name();
        let type_info = column.type_info();
        if type_info.name() == "TEXT" {
            let cval: &str = row.get(cname);
            json_row.insert(cname.to_string(), json!(cval));
        } else if type_info.name() == "INTEGER" {
            let cval: i64 = row.get(cname);
            json_row.insert(cname.to_string(), json!(cval));
        }
    }
    json_rows.push(json_row);
}
let json_rows = json!(json_rows);
println!("JSON ROWS: {}", json_rows);

The output of that last println statement will look something like: JSON ROWS: [{"col1":"foo","col2":1,"col3":"car"}].

Note that the type name (TEXT and INTEGER in the above example) may be database specific, since type_info is actually an AnyTypeInfo struct (or enum?) that in this case happens to look like: AnyTypeInfo(Sqlite(SqliteTypeInfo(Text))) and the AnyTypeInfo::name() method might in principle return something different depending on whether it is actually a SqliteTypeInfo or struct or something corresponding to another database type.

[lmcmicu]

I just updated my previous comment. I wasn't aware, at first, of the TypeInfo::name() method which we should be using instead of .to_string() here (although name() is likely the way this type implements .to_string() in any case). This doesn't make any difference to the database-sensitivity issue.

[lmcmicu]

That said I don't see why the json_object() approach wouldn't also work. The PostgreSQL equivalent seems to be the row_to_json() function, which you can call like this:

select row_to_json(t) from (select row_number, prefix, base, "ontology IRI", "version IRI" from table1) t

with the returned result looking like:

                                                   row_to_json                                                   
-----------------------------------------------------------------------------------------------------------------
 {"row_number":1,"prefix":"","base":"zdkxksnil","ontology IRI":null,"version IRI":null}
 {"row_number":2,"prefix":"igxqqtute","base":"jksxsmspa","ontology IRI":"fdzhfneqa","version IRI":"mpjzhusus"}
 {"row_number":3,"prefix":"dgqcgjinp","base":"vknrbxiqb","ontology IRI":"irlklwmhh","version IRI":"hbjcwwsgc"}
 {"row_number":4,"prefix":"xaikwlbuh","base":"skawpipzl","ontology IRI":"aljnrlvym","version IRI":"hbznkujra"}
 {"row_number":5,"prefix":"jrywcogbc","base":"ydsksufhu","ontology IRI":"zvlrohbuw","version IRI":"xpbhnjesy"}
 {"row_number":6,"prefix":"majoiuino","base":"jbviyllmf","ontology IRI":"jhsypwxdt","version IRI":"gtxdxkggq"}
 {"row_number":7,"prefix":"otmwsmgjl","base":"iiwyjmrga","ontology IRI":null,"version IRI":null}
...

[lmcmicu]

You can also call row_to_json() without knowing the column names. This syntax also works: select row_to_json(table1) from table1