JSON_NORMALIZE

Discover JSON_NORMALIZE in MariaDB. This function recursively sorts keys and removes spaces from a JSON document, enabling reliable equality comparisons and unique constraints on JSON data.

JSON_NORMALIZE is available from MariaDB 10.7.

Syntax

JSON_NORMALIZE(json)

Description

Recursively sorts keys and removes spaces, allowing comparison of json documents for equality.

Examples

We may wish our application to use the database to enforce a unique constraint on the JSON contents, and we can do so using the JSON_NORMALIZE function in combination with a unique key.

For example, if we have a table with a JSON column:

CREATE TABLE t1 (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 val JSON,
 /* other columns here */
 PRIMARY KEY (id)
);

Add a unique constraint using JSON_NORMALIZE like this:

ALTER TABLE t1
   ADD COLUMN jnorm JSON AS (JSON_NORMALIZE(val)) VIRTUAL,
   ADD UNIQUE KEY (jnorm);

We can test this by first inserting a row as normal:

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

And then seeing what happens with a different string which would produce the same JSON object:

INSERT INTO t1 (val) VALUES ('{ "color": "blue", "name": "alice" }');
ERROR 1062 (23000): Duplicate entry '{"color":"blue","name":"alice"}' for key 'jnorm'

See Also

• JSON_EQUALS

_{This page is licensed: CC BY-SA / Gnu FDL}