MySQL and MariaDB JSON From Rust: Duplicate Keys and Nulls

Article · Derived state

A Rust service stores an incoming JSON body and later extracts one field. During a MySQL-to-MariaDB migration, a document with a repeated field name can return a different value. Both inserts can succeed. The raw text can also come back differently.

Here is a document containing the member a twice. This is poor input for an application contract, but it is useful test input. MySQL and MariaDB accept JSON columns through the same Rust driver while giving this document different treatment. A successful insert alone would hide the difference.

The fixture sends the original string directly. It does not parse the value into a map first. Many parsers resolve duplicate members while constructing an object, so parsing and serializing in Rust could remove the behavior we want to inspect before the database sees it.

Compare storage and extraction separately

The example uses SQLx 0.9.0, MySQL 8.4.13 and MariaDB 11.4.13. The connection helper checks each reported server version against its test pin. That check is separate from TLS certificate verification; a version string does not authenticate the server. Both tables declare a JSON column, but that matching declaration does not imply matching representation.

async fn compare(variable: &str) -> Result<()> {
    let mut c = connect(variable).await?;
    sqlx::query("DROP TABLE IF EXISTS b_json")
        .execute(&mut c)
        .await?;
    sqlx::query("CREATE TABLE b_json(id INT PRIMARY KEY, payload JSON NOT NULL) ENGINE=InnoDB")
        .execute(&mut c)
        .await?;
    let original = r#"{ "a":1, "a":2, "note":"é" }"#;
    sqlx::query("INSERT INTO b_json VALUES(1,?)")
        .bind(original)
        .execute(&mut c)
        .await?;
    let stored: String = sqlx::query_scalar("SELECT CAST(payload AS CHAR) FROM b_json WHERE id=1")
        .fetch_one(&mut c)
        .await?;
    let selected: String = sqlx::query_scalar(
        "SELECT JSON_UNQUOTE(JSON_EXTRACT(payload,'$.a')) FROM b_json WHERE id=1",
    )
    .fetch_one(&mut c)
    .await?;
    if variable == "MARIADB_URL" {
        assert_eq!(stored, original);
        assert_eq!(selected, "1");
    } else {
        assert_ne!(stored, original);
        assert_eq!(selected, "2");
    }
    sqlx::query("INSERT INTO b_json VALUES(2,?)")
        .bind(r#"{"n":null}"#)
        .execute(&mut c)
        .await?;
    let explicit: Option<String> =
        sqlx::query_scalar("SELECT JSON_TYPE(JSON_EXTRACT(payload,'$.n')) FROM b_json WHERE id=2")
            .fetch_one(&mut c)
            .await?;
    let missing: Option<String> = sqlx::query_scalar(
        "SELECT JSON_TYPE(JSON_EXTRACT(payload,'$.absent')) FROM b_json WHERE id=2",
    )
    .fetch_one(&mut c)
    .await?;
    assert_eq!(explicit.as_deref(), Some("NULL"));
    assert_eq!(missing, None);
    let rejected = sqlx::query("INSERT INTO b_json VALUES(3,?)")
        .bind("not json")
        .execute(&mut c)
        .await
        .unwrap_err();
    let expected = if variable == "MARIADB_URL" {
        4025
    } else {
        3140
    };
    assert_eq!(number(&rejected), Some(expected));
    let absent: i64 = sqlx::query_scalar("SELECT COUNT(*) FROM b_json WHERE id=3")
        .fetch_one(&mut c)
        .await?;
    assert_eq!(absent, 0);
    println!("{variable}: stored={stored}; selected a={selected}; null and missing checked");
    Ok(())
}

The complete source is mysql_mariadb_json.rs. It drops only its fixture table in each disposable schema. For the first row, it reads both the stored document as text and the value selected by a JSON path.

MySQL's JSON documentation describes its native representation and normalization, including duplicate-key handling. MariaDB documents JSON as a LONGTEXT alias with validation, retention of the assigned text and access to the first duplicate member through functions such as JSON_EXTRACT. For this input, MySQL extraction should select two and MariaDB extraction should select one. MariaDB should also return the original document text, while MySQL should return a normalized representation. The assertions encode those expected differences for the pinned versions. They still need the recorded engine run; no returned document shown here is invented as a run result.

No performance result follows from this comparison. A native representation and a text-backed representation may affect operations differently, but this small fixture measures neither storage efficiency nor query latency. It checks what a stored value means to the application.

Duplicate members are a boundary problem

RFC 8259 recommends unique names within an object and explains the interoperability problem when names repeat. A database accepting a document does not mean every consumer will interpret it the same way.

Suppose one system checks the first role member while another reads the last. Rejecting duplicates before the document enters that workflow is safer than hoping every parser agrees. The validator must actually observe duplicate members. A generic parse into an ordinary key-value map may already have discarded the information needed to reject them.

If duplicate members are not allowed, make that a documented input rule and test it at the API boundary. This fixture deliberately stores them to expose engine behavior; it is not a recommendation to keep ambiguous documents in application records.

A similar issue appears with signing or hashing. If a signature covers the exact request bytes, a JSON column that changes formatting or representation is not an archive of those bytes. Store the original payload separately when the original is required, with the appropriate access and retention controls. If the protocol defines a canonical representation, implement that protocol explicitly. Do not invent one by relying on whichever text a database happens to return.

Missing and null ask different questions

The second row contains a field whose value is JSON null. Another field is absent. JSON_TYPE returns the string NULL for the present null value, while the missing path produces SQL NULL. The fixture therefore expects Some("NULL") and None in Rust. The MariaDB JSON_TYPE reference documents that distinction.

This distinction matters for updates. “No new value supplied” can mean preserve the old value, while an explicit null can mean clear it. Mapping both to one Rust option without a defined API rule can lose the difference before persistence. The exact Rust representation is a design choice; the important part is retaining the distinction until the operation decides what to do.

The invalid-document insert must fail with MySQL error 3140 or MariaDB error 4025, and the rejected row must remain absent. A generic connection error would not check JSON validation. Even the expected rejection only checks JSON syntax: it says nothing about the application's required fields, positive quantities or references to other records.

Carry the contract into a migration

Build a short collection of actual document shapes before moving engines. Include absent fields, explicit nulls, Unicode text, large numbers and values that queries extract for filtering. If duplicates are forbidden, include them as expected rejections at the application boundary as well as a diagnostic database probe.

Numeric values need special attention when another consumer uses floating-point numbers. An integer can survive database storage and still lose precision in a later client. Add a large value from your accepted range, then check its extracted type and the value received by that client.

Keep the original inputs when comparing results. Normalizing both outputs into the same Rust value can be a useful semantic comparison, but it should be a second check. Otherwise it can make a storage or extraction difference disappear from the report.

The broader Rust compatibility contract tests ordinary columns and rollback. For the migration checklist, keep the raw duplicate-member input, the selected a value and the null-versus-missing result separately. If the service needs the original request for a signature or archive, decide where those bytes will live before moving the table. A successful insert cannot make that decision for the next consumer.