Error: Error { kind: ToSql(0), cause: Some(WrongType { postgres: Json, rust: "alloc::string::String" }) }

Question

I have tried to insert json value into my table and got error

async fn insert_values(client: &Client, user: Report) -> Result<(), Error> {
    // Serialize the user data to JSON
    let user_json = json!({
        "username": user.username,
        "gender": {
            "val": user.gender.val,
        },
    });
     let res = serde_json::to_string(&user_json ).unwrap();
    // Execute the SQL statement to insert values
    client
        .execute("INSERT INTO users (user_report) VALUES ($1)", &[&res])
        .await?;
    Ok(())
}

like this Error: Error { kind: ToSql(0), cause: Some(WrongType { postgres: Json, rust: "alloc::string::String" }) }

here is create table function

async fn create_table(client: &Client) -> Result<(), Error> {
    // Define the SQL statement to create a table if it doesn't exist
    let command = r#"
        CREATE TABLE IF NOT EXISTS users (
            id SERIAL PRIMARY KEY,
            user_report JSONB
        )"#;

    // Execute the SQL statement to create the table
    client.execute(command, &[]).await?;
    Ok(())
}

and Cargo.toml

[dependencies]
serde = {version = "1.0.164", features=["derive"]}
serde_json = "1.0.103"
tokio-postgres = [version = "0.7.10", features= ["with-serde_json-1"]]
tokio = { version = "1", features = ["full"] }

output error is Error: Error { kind: ToSql(0), cause: Some(WrongType { postgres: Json, rust: "alloc::string::String" }) } , code side is compiled without any errors but in posgre itself some this kind of error appears

Answer 1

Actually, looks like Json is not necessary if feature flag with-serde_json-1 is activated. Consequently, I'll suggest using @pr0gramista's answer, which is slightly more concise.

---

tokio-postgres has a struct Json, hidden behind feature flag with-serde_json-1.

use tokio_postgres::types::Json;


async fn insert_values(client: &Client, user: Report) -> Result<(), Error> {
    // Serialize the user data to JSON
    let user_json = Json(json!({ // <-- `Json` can be bound into SQL.
        "username": user.username,
        "gender": {
            "val": user.gender.val,
        },
    }));
    // Execute the SQL statement to insert values
    client
        .execute("INSERT INTO users (user_report) VALUES ($1)", &[&user_json])
        .await?;
    Ok(()
}

I'm using the following dependencies

[dependencies]
serde = "1.0.192"
serde_json = "1.0.108"
tokio = { version = "1.33.0", features = ["full"] }
tokio-postgres = { version = "0.7.10", features = ["with-serde_json-1"] }

Answer 2

Looking at trait implementation I see that the trait ToSql is implemented for serde_json Value enum, not String.

If you have a struct that implements Serialize you can serialize it to Value using serde_json::to_value(...).

You can skip serialization to String and pass Value directly:

async fn insert_values(client: &Client, user: Report) -> Result<(), Error> {
    // Serialize the user data to JSON
    let user_json = json!({
        "username": user.username,
        "gender": {
            "val": user.gender.val,
        },
    });
    // Execute the SQL statement to insert values
    client
        .execute("INSERT INTO users (user_report) VALUES ($1)", &[&user_json])
        .await?;
    Ok(()
}