Is there some way I can parse JSON fields as strings - and/or arbitrary types that serde can deserialize

[rib]

Hi I've been experimenting with switching my codebase over to using SQLx but have been hitting a few niggles with the handling of JSON fields...

I've been wishing I could avoid the implicit deserializing of JSON fields into serde_json::Values since it's leading me to have to deserialize multiple times redundantly where Value isn't the type I'd like. Additionally in error paths where I used to try and pretty print the original json data I'm not currently able to do that because I have to move the Value to deserialize it using serde_json::from_value() and so I've got nothing detailed left to print if something goes wrong while deserializing it (unless I clone the values just in case I hit a future error, which isn't scalable for me).

To try and keep the JSON fields as strings I've tried doing something like this:

    #[derive(sqlx::FromRow)]
    struct Record {
        customStats: Option<String>,
        stats: Option<String>
    }
    let record = sqlx::query_as!(Record, "SELECT customStats, stats FROM Users WHERE userId = ?;", uid)
        .fetch_one(&mut conn).await?;

but as far as I can see atm SQLx will only be happy if deserializing JSON into a serde_json::Value.

As a hacky workaround I was able to use the macros if I added explicit casts to the sql statement like:

let record = sqlx::query_as!(Record, "SELECT CAST(customStats as CHAR) as customStats, CAST(stats AS CHAR) as stats FROM Users WHERE userId = ?;", uid)
        .fetch_one(&mut conn).await?;

which results in LONGTEXT fields but that's really cumbersome and it's unclear exactly what additional redundant work might be done as part of the cast by the server.

When I tried a dynamic query I got a runtime error if I tried to deserialize the JSON as Option<Strings> and if I tried Option<&str> I got a compiler error about the FromRow implementation not being generic enough. I took at stab at manually implementing the trait as follows:

    struct Record<'a> {
        customStats: Option<&'a str>,
        stats: Option<&'a str>
    }
    impl<'r> sqlx::FromRow<'r, MySqlRow> for Record<'r> {
        fn from_row(row: &'r MySqlRow) -> Result<Self, Error> {
            let customStats = row.try_get::<Option<&'r str>, _>("customStats")?;
            let stats = row.try_get::<Option<&'r str>, _>("stats")?;
            Ok(Record {
                customStats, stats
            })
        }
    }

    let record: Record = sqlx::query_as("SELECT customStats, stats FROM Users WHERE userId = ?;")
        .bind(uid)
        .fetch_one(&mut conn).await?;

But I'm getting an error like:

implementation of `FromRow` is not general enough
`FromRow<'0, MySqlRow>` would have to be implemented for the type `show_user_stats::{closure#0}::Record<'_>`, for any lifetime `'0`...
...but `FromRow<'1, MySqlRow>` is actually implemented for the type `show_user_stats::{closure#0}::Record<'1>`, for some specific lifetime `'1`

and I didn't get my head around that error so far. (show_user_stats is the name of the enclosing function)

I wonder if there's maybe some trick I'm missing for being able to get SQLx to coerce JSON into a String or &str instead of a Value? If not it would be great to have a bit more control here.

In addition to getting JSON fields as plain Strings it might also be good if the macro would accept anything that can be deserialized by serde - so the field could effectively be deserialized in from_row via serde_json::from_str::<MyType>(field)

[rib]

After a bit more poking around I realised a few things...

I found that there's a generic Json<T> type that can be used to deserialize json fields into arbitrary deserializable types besides serde_json::Value

I realised there was enough public api to actually define another type that would also be accepted for json fields (at least with dynamic queries) and I since I could implement the decode I could just pass back the original string; something like this...

#[derive(
    Clone, Debug, PartialEq, Eq, PartialOrd, Ord, Hash, Default, Serialize, Deserialize,
)]
#[serde(transparent)]
pub struct JsonString(pub String);
impl Deref for JsonString {
    type Target = String;

    fn deref(&self) -> &Self::Target {
        &self.0
    }
}

impl DerefMut for JsonString {
    fn deref_mut(&mut self) -> &mut Self::Target {
        &mut self.0
    }
}

impl<'r> AsRef<str> for JsonString {
    fn as_ref(&self) -> &str {
        self.0.as_str()
    }
}

impl<'r> Decode<'r, MySql> for JsonString
{
    fn decode(value: MySqlValueRef<'r>) -> Result<Self, BoxDynError> {
        let str_value = <&'r str as Decode<MySql>>::decode(value)?;
        Ok(JsonString(str_value.to_string()))
    }
}

impl<'r> Type<MySql> for JsonString {
    fn type_info() -> MySqlTypeInfo {
        <serde_json::Value as Type<MySql>>::type_info()
    }

    fn compatible(ty: &MySqlTypeInfo) -> bool {
        <serde_json::Value as Type<MySql>>::compatible(ty)
            || <&str as Type<MySql>>::compatible(ty)
            || <&[u8] as Type<MySql>>::compatible(ty)
    }
}

So then I could define a record type like:

   #[derive(sqlx::FromRow)]
    struct Record {
        customStats: Option<JsonString>,
        stats: Option<JsonString>
    }

Notably though I was only able to query via a dynamic query_as and trying to use the query_as! macro with build time validation was still expecting a serde_json::Value and failing to compile.

I also tried to implement a similar type that would let me borrow a &str from original value from the database but I wasn't able to use it due to lifetime complaints when using it within an async function...

#[derive(
    Copy, Clone, Debug, PartialEq, Eq, PartialOrd, Ord, Hash, Default, Serialize, Deserialize,
)]
#[serde(transparent)]
pub struct JsonStr<'r>(pub &'r str);
impl<'r> Deref for JsonStr<'r> {
    type Target = str;

    fn deref(&self) -> &Self::Target {
        &self.0
    }
}

impl<'r> AsRef<str> for JsonStr<'r> {
    fn as_ref(&self) -> &'r str {
        self.0
    }
}

impl<'r> Decode<'r, MySql> for JsonStr<'r>
{
    fn decode(value: MySqlValueRef<'r>) -> Result<Self, BoxDynError> {
        Ok(JsonStr(<&'r str as Decode<MySql>>::decode(value)?))
    }
}

impl<'r> Type<MySql> for JsonStr<'r> {
    fn type_info() -> MySqlTypeInfo {
        <serde_json::Value as Type<MySql>>::type_info()
    }

    fn compatible(ty: &MySqlTypeInfo) -> bool {
        <serde_json::Value as Type<MySql>>::compatible(ty)
            || <&str as Type<MySql>>::compatible(ty)
            || <&[u8] as Type<MySql>>::compatible(ty)
    }
}