Filtering for json, but sea_orm filters for jsonb instead. Postgresql

[hldup]

I have an enum that I save into my postgresql database as a Json value with serde_json.

pub enum BelongsTo {
    User(Uuid),
    Group(Uuid),
}

When I try to search for it I get an error from the databasa saying I cannot compare json = jsonb. I don't really understand why its trying to query with a JsonBinary instead of normal Json.

    crate::entities::job::Entity::find()
            .filter(entities::job::Column::BelongsTo.eq(serde_json::to_value(belongs_to).unwrap()))
            .all(db)
            .await

Query Error: error returned from database: operator does not exist: json = jsonb

I have also double checked, and indeed my type is json in the entities.
Any ideas why this could be happening?

[emmiegit]

It seems that SeaORM defaults to using JSONB as the type when emitting SQL for Postgres? I am not seeing docs indicating how to select which of JSON vs JSONB is desired for a particular case, and this seems to also happen when doing ops on columns even if they are JSON (and not JSONB).

[tyt2y3]

I am aware of this, you have to cast as for now:

.filter(
    Expr::col(COLUMN.json).eq(Expr::val(json!({ "id": 22 })).cast_as("json"))
)

Not sure how can we abstract this, may be?

.filter(COLUMN.json.eq(json!({ "id": 22 }))) // <- can we actually tell that the column type is not jsonb?
.filter(COLUMN.json.eq_json(json!({ "id": 22 }))) // at least we can do this and apply cast_as on the value

[tyt2y3]

@Huliiiiii got a better idea?

[emmiegit]

In my opinion, if the column is JSON then it should try to treat the RHS as JSON, and if it is JSONB then it should treat the RHS there as JSONB.

At present it is always using JSONB even if the column is known by SeaORM to be JSON. This necessitates a manual cast like in tyt2y3's example.

[Huliiiiii]

This is sqlx's behavior, and we can't change it...
We should add it to documentation.

[Huliiiiii]

Perhaps we can add some custom logic to implementations of TypedColumn. However, other APIs still need explicit casting.