MySQL JSON diff

Simple function that compares two JSON structures (on 1st level only), and returns old values for changed keys. I’m using it to track table changes via before update trigger.

Usage is simple, feed it with JSONs constructed from OLD.* and NEW.* values.


CREATE FUNCTION diff_json_get_old(json_old JSON, json_new JSON) RETURNS JSON DETERMINISTIC
BEGIN
DECLARE v_json JSON DEFAULT JSON_OBJECT();
DECLARE v_json_keys JSON DEFAULT JSON_KEYS(JSON_MERGE(json_new, json_old));
DECLARE v_value_old VARCHAR(256);
DECLARE v_value_new VARCHAR(256);
DECLARE v_length TINYINT UNSIGNED DEFAULT JSON_LENGTH(v_json_keys);
DECLARE v_idx TINYINT UNSIGNED DEFAULT 0;
DECLARE v_key VARCHAR(32);

WHILE v_idx < v_length DO
SET v_key := JSON_EXTRACT(v_json_keys, CONCAT('$[', v_idx, ']')),
v_value_old := JSON_EXTRACT(json_old, CONCAT('$.', v_key)),
v_value_new := JSON_EXTRACT(json_new, CONCAT('$.', v_key));

IF (v_value_old <> v_value_new OR v_value_new IS NULL) THEN
SET v_json := JSON_SET(v_json, CONCAT('$.', v_key), v_value_old);
END IF;

SET v_idx := v_idx + 1;
END WHILE;

-- nothing changed = return NULL
IF JSON_LENGTH(v_json) = 0 THEN
RETURN NULL;
ELSE
RETURN v_json;
END IF;
END