Menu

MariaDB JSON_NORMALIZE(): Canonicalize JSON Documents

MariaDB JSON_NORMALIZE() recursively sorts object keys and removes formatting whitespace from a JSON document. It is available from MariaDB 10.7. See the official MariaDB JSON_NORMALIZE reference.

Syntax

JSON_NORMALIZE(json)

Normalize a JSON document

Documents with the same values but different object-key order or whitespace normalize to the same text:

SELECT JSON_NORMALIZE('{ "color": "blue", "name": "alice" }');

{"color":"blue","name":"alice"}

For a JSON-aware equality check without comparing normalized strings, use JSON_EQUALS().

Use normalized JSON in a unique key

MariaDB’s documentation demonstrates a virtual generated column to enforce uniqueness for equivalent JSON object text:

CREATE TABLE documents (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    val JSON,
    normalized JSON AS (JSON_NORMALIZE(val)) VIRTUAL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_documents_normalized (normalized)
);

Insert one document:

INSERT INTO documents (val)
VALUES ('{"name":"alice","color":"blue"}');

The following document has the same key-value pairs in a different order and with different whitespace. Its normalized value conflicts with the unique key, so the insert fails with a duplicate-key error:

INSERT INTO documents (val)
VALUES ('{ "color": "blue", "name": "alice" }');

This technique enforces uniqueness according to MariaDB’s normalized JSON text. For direct JSON-aware equality checks, use JSON_EQUALS() instead.

Advertisement