What is the error?

UnexpectedNullError:
ColumnDecode { index: "\"data\"", source: UnexpectedNullError }

Okay, that's why I asked because on first blush I thought you were getting a compiler error. That's why it's important to specify exactly what error you're getting.

So the problem is that #[sqlx(json)] transforms the field to Json<Option<Data>>, which means it can handle the JSON string 'NULL' but not the SQL value NULL. That does seem rather surprising on review but the transformation is quite naive.

Now, it's quite possible that someone might actually want it to work that way, so unfortunately we can't just change it to output Option<Json<Data>> now without it being a breaking change.

However, there might be another option.

I think it would be relatively easy to specialize Json<Option<Data>> to accept either 'NULL' or NULL . If it gets a null column error, it could just attempt to decode from a literal 'NULL' instead, That would even let it work with types that borrow from the deserializer still.

I'm not sure if that would be too surprising or not. Do you think anyone would actually ever need to distinguish between 'NULL' and NULL?

I'm sorry for the confusion, I won't forget it next time :D

I think it would be relatively easy to specialize Json<Option<Data>> to accept either 'NULL' or NULL . If it gets a null column error, it could just attempt to decode from a literal 'NULL' instead, That would even let it work with types that borrow from the deserializer still.

I'm not sure if that would be too surprising or not. Do you think anyone would actually ever need to distinguish between 'NULL' and NULL?

I cannot think of any situation where someone would need to distinguish between the these, so I really like this solution.

Another possbile solution would be something like #[sqlx(json(nullable))] that converts Option<Data> to Option<Json<Data>> , but I'm not to sure about that.

Yeah, #[sqlx(json(nullable))] was the other option I was thinking.

Thinking about this more, I'm wary of quietly changing the behavior of Json in a minor release, even if it's hard to imagine why someone would care. Though #[sqlx(json(nullable)]] feels less elegant, it would at least be backwards-compatible.

@abonander is there any update on this? I am getting the same error.

We'd always welcome a pull request.

I was also impacted by this today! I don't know what to do now. Why is an Optional field saved in the DB with the text "null"?

This is really strange.

Anyway thanks all of you for your hard work! ♥

for now I done this in inelegant way and hope there will be official way for this

1. remove #[sqlx(json)]
2. remove singer? in SQL(same result but no need to keep it)
3. use json_build_object return json value from SELECT
4. implementation FromRow deserialize json value for spec field(singer: Option)

        CASE WHEN songs.singer_id IS NOT NULL THEN
            json_build_object('id', a3.id, 'name', a3.name)
        ELSE
            NULL
        END AS singer

#[derive(Debug, PartialEq, Deserialize, Serialize, sqlx::FromRow)]
pub struct Artist {
    pub id: Uuid,
    pub name: String,
    pub created_at: DateTime<Utc>,
}

#[derive(Debug, PartialEq, Deserialize, Serialize)]
pub struct Song {
    pub id: Uuid,
    pub name: String,
    pub singer: Option<Artist>,
    pub updated_at: DateTime<Utc>,
}

impl FromRow<'_, PgRow> for Song {
    fn from_row(row: &PgRow) -> sqlx::Result<Self> {
        Ok(Self {
            id: row.get("id"),
            name: row.get("name"),
            // here we check if null
            singer: match row.try_get::<JsonValue, &str>("singer") {
                Ok(singer) => Some(Artist::deserialize::<JsonValue>(singer).unwrap()),
                Err(_) => None,
            },
            updated_at: row.get("updated_at"),
        })
    }
}