If I insert an `Option<sqlx::types::Json<Struct>>` sqlx writes the text `null` in DB instead of mark that field as `NULL`

I thought the problem was me and my code and so I tried to reproduce the problem with sqlx example directly.

I only made minor changes by adding an Option<> to simulate my real problem.


- async fn add_person(pool: &PgPool, person: Person) -> anyhow::Result<i64> {

+ async fn add_person(pool: &PgPool, person: Option<Person>) -> anyhow::Result<i64> {

and in migrations:


- person JSONB NOT NULL

+ person JSONB

If I run it it is saved in the DB:

image

and instead I expect this:

image

Where am I doing wrong?

Minimal Reproduction

Info

  • SQLx version: "0.7.4"

  • SQLx features enabled: "macros", "postgres", "runtime-tokio", "chrono", "uuid"

  • Database server and version: Postgres 16

  • Operating system: Windows

  • rustc --version: rustc 1.79.0 (129f3b996 2024-06-10)

JSON has its own nullable type, null, so if you ask for JSON serialization of a nullable value, you will get the valid JSON string null. And you are asking for exactly that, since you are wrapping the whole person argument (which is Option<Person>) into Json.

I guess you should instead ask for NULL-or-JSON, i.e., Option<Json<Person>> instead of Json<Option<Person>>, which is person.map(Json).

2 Likes

You're inserting a value of type Json<Option<Person>>, not of type Option<Json<Person>>, via the constructor Json(person). That encodes the None case as Json(None), which, indeed, represents a non-SQL-null JSON value that encodes as null.

You likely want to transpose the type to Option<Json<Person>> (indeed, you name it that in your title). You can do this with something like person.map(|value| Json(value)), where you presently have Json(person).

1 Like

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.