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

Leave a Reply

Your email address will not be published. Required fields are marked *

*