Sqlx fetching typed json

I'm trying to fetch sqlx::Json but getting: expected struct Json, found enum JsonValue. Here's the code:

//! ```cargo
//! [dependencies]
//! dotenv = "0.15"
//! serde = { version = "1", features = ["derive"] }
//! sqlx = { version = "0.6", features = ["runtime-tokio-native-tls", "postgres", "json"] }
//! tokio = { version = "1", features = ["full"] }
//! ```

#[allow(dead_code)]
#[derive(Debug, serde::Deserialize)]
struct MyJson {
    id: i32,
}

#[allow(dead_code)]
#[derive(Debug, sqlx::FromRow)]
struct MyRow {
    my_json: Option<sqlx::types::Json<MyJson>>,
}

#[tokio::main]
async fn main() -> sqlx::Result<()> {
    dotenv::dotenv().ok();
    let db = sqlx::PgPool::connect(&std::env::var("DATABASE_URL").unwrap()).await?;

    let rows = sqlx::query_as!(
        MyRow,
        r#"
        WITH
            my_row ("my_json") AS (VALUES
                ('{"id": 1}'::jsonb)
            )
        SELECT *
        FROM my_row
        "#,
    )
    .fetch_all(&db)
    .await?;

    dbg!(rows);
    Ok(())
}

And the error:

   |                                                                                                                                           
29 |       let rows = sqlx::query_as!(                                                                                                         
   |  ________________^                                                                                                                        
30 | |         MyRow,                                                                                                                          
31 | |         r#"                                                                                                                             
32 | |         WITH                                                                                                                            
...  |                                                                                                                                         
38 | |         "#,                                                                                                                             
39 | |     )                                                                                                                                   
   | |_____^ expected struct `Json`, found enum `JsonValue`                                                                                    
   |                                                                                                                                           
   = note: expected enum `std::option::Option<Json<MyJson>>`                                                                                   
              found enum `std::option::Option<JsonValue>`                                                                                      
   = note: this error originates in the macro `$crate::sqlx_macros::expand_query` which comes from the expansion of the macro `sqlx::query_as`

I managed to do it explicitly typing the column: SELECT my_json as "my_json!: sqlx::types::Json<MyJson>". But the question still remains in case of asterix.

query_as! doesn't have any knowledge of the types in your struct. Since it can't possibly know what type to parameterize Json with, it has to use JsonValue for any JSON columns it encounters unless you specifically tell it to use something else.

I solved this by deserializing twice. Sqlx deserializes to jsonvalue and then I deserialize the jsonb value to the correct struct.

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.