Convert the result of sqlx::query! to JSON

[omikuji]

Hi!

I want convert the result of sqlx::query! to JSON.

My ideals are as follows. This one doesn't work.

let examples = sqlx::query!(
    "SELECT id, name FROM example",
)
.fetch_all(&**pool)
.await?;

let json = serde_json::to_string(&examples);

alternative means

#[derive(sqlx::FromRow, serde::Serialize)]
struct Example {
    id: i32,
    name: String,
}

let examples = sqlx::query_as::<_, Example>(
    "SELECT id, name FROM example",
)
.fetch_all(&**pool)
.await?;

let json = serde_json::to_string(&examples);

Do you have any other good ideas?

Thank you!

[abonander]

If you're using Postgres, you can aggregate the query result to JSON and then get it out that way:

// use non-null override `"json!"` since this is an expression
// `r#` prefix is so we can use double-quotes in the string
let examples = sqlx::query!(r#"select json_agg(json_build_object('id', id, 'name', name))::text "json!" from example"#)
    .fetch_one(&**pool)
    .await?;

let json = examples.json;

Otherwise, your second example is close but there is a macro version of query_as! which doesn't require sqlx::FromRow:

#[derive(serde::Serialize)]
struct Example {
    id: i32,
    name: String,
}

let examples = sqlx::query_as!(
    Example,
    "SELECT id, name FROM example",
)
.fetch_all(&**pool)
.await?;

let json = serde_json::to_string(&examples);