Serialize SQL output as JSON?


#1

Hello, I’m working on an application which is basically a search API for some PHP software we’re using. I’m trying to use the Rocket framework which is going well, and I’m able to execute a SQL query and output the data onto the page but I’d like to format it as JSON. How can I do this using something like serde_json? Currently I manually manufacture my data output like this:

                Ok(rows) => {
                let mut users_string = String::new();
                let mut num_users = 0;

                for row in rows.iter() {
                    let user_id: i32 = row.get("u");
                    let user_name: String = row.get("n");

                    users_string.push_str(&user_id.to_string());
                    users_string.push_str(": ");
                    users_string.push_str(&user_name);
                    users_string.push_str(", ");

                    num_users += 1;
                }

                if num_users > 0 {
                    let users_string_len = users_string.len();
                    users_string.truncate(users_string_len - 2)
                }

                users_string
            },

Instead, I would like to have some JSON output like this:

[{u:'12', n:'cool user'}]

Ideas, tutorials and tips very welcome. :slight_smile:

Thanks!


#2

Use an intermediate struct, and derive Serialize (from serde_derive):

#[macro_use] extern crate serde_derive;
extern crate serde;
extern crate serde_json;

#[derive(Debug, Serialize, PartialEq, Eq)]
struct User {
    id: i32,
    name: String,
}

impl User {
    pub fn new(id: i32, name) -> User {
        User { id, name }
    }
}


fn rows_to_user(rows: YourRowsType) -> Result<User> {
    let users = Vec::with_capacity(rows.len());
    for row in rows { // .iter is redundant here
        users.push(Ok(User::new(row.get("u")?, row.get("n")?));  // Presumably row.get is fallible
    }
    users
}

fn users_to_json(users: Vec<User>) -> Result<String> {
    serde_json::ser::to_string(users)
}

fn main() {
    let rows = your_get_rows();
    println!("{}", users_to_json(rows_to_users(rows).expect("Users from database")).expect("JSON from users"));
}

#3

For trivial cases, you can also use the json! macro to build the json output. But as @cliff suggested, a struct is the way to go most of the time.

You can rename fields so the json tags have different name from the struct (eg u instead of id) using https://serde.rs/field-attrs.html#serderename--name; take a look at the other attributes to familiarize yourself with out-of-the-box customization options.