[SOLVED] SQLx treats JsonValue field as if it is always a string

SQLx treats JsonValue field as if it is always a string

I have a struct which derives sqlx::FromRow with a field of type JsonValue. The corresponding field in the SQLite database is of type VARCHAR. The problem is, when loading from the database, the value always comes out as a JsonValue::String(...).

Example:

CREATE TABLE IF NOT EXISTS foo (bar VARCHAR);
INSERT INTO foo (bar) VALUES ('{}');

#[derive(sqlx::FromRow)]
pub struct Foo {
    pub bar: sqlx::types::JsonValue,
}

#[tokio::main]
async fn main() -> Result<(), sqlx::Error> {
    let db = sqlx::sqlite::SqlitePoolOptions::new()
        .connect("sqlite://test.db")
        .await
        .unwrap();
    
    let foo = sqlx::query_as!(Foo, "SELECT bar from foo LIMIT 1")
        .fetch_one(&db)
        .await?;
    
    print!("{:?}\n", &foo.bar);
    
    Ok(())
}

The output is String("{}"). The expected output for this example is Object {}, since the actual value in the database is {}, not "{}". How can I make this work?

Solution

The sqlx::query_as! macro allows the column type to be annotated in the query as follows:

sqlx::query_as!(Foo, "SELECT bar \"bar: JsonValue\" from foo LIMIT 1")

The type JsonValue needs to be in scope, so either use sqlx::types::JsonValue; or qualify it in the query. With this change, the example code prints Object {} as required.

You may also want to change the column type in the database to JSON instead of VARCHAR, but this is not strictly needed to make SQLx behave as expected here.