3
\$\begingroup\$

I'm building a procedure which compares two JSON objects and return two JSON objects with differences between each (compared keys and values). But I have relatively superficial SQL knowledge, so I need your review to avoid any simple mistakes and improve my code.

Compares "first" against "second" and returns the values in "first" that are not present in "second" OR has different value

DELIMITER $$

DROP PROCEDURE IF EXISTS json_diff $$

# Compares "first" against "second" and
# returns the values in "first" that are not present in "second" OR has different value
CREATE PROCEDURE json_diff(IN first JSON, IN second JSON, INOUT firstResult JSON, INOUT secondResult JSON)
BEGIN
    DECLARE oldKey VARCHAR(300);
    DECLARE oldVal, newVal INT;
    DECLARE i, secondHasKey INT DEFAULT 0;
    DECLARE oldKeys, newKeys JSON;

    SELECT JSON_KEYS(first) INTO oldKeys;
    SELECT JSON_KEYS(second) INTO newKeys;

    WHILE i < JSON_LENGTH(oldKeys) DO
        SELECT JSON_EXTRACT(oldKeys, CONCAT('$[',i,']')) INTO oldKey;
        SELECT JSON_EXTRACT(first, CONCAT('$.', oldKey)) INTO oldVal;

        SELECT JSON_CONTAINS_PATH(second, 'one', CONCAT('$.', oldKey)) INTO secondHasKey;

        IF secondHasKey = 1 THEN
            SELECT JSON_EXTRACT(second, CONCAT('$.', oldKey)) INTO newVal;
            IF oldVal != newVal THEN
                SELECT JSON_INSERT(firstResult, CONCAT('$.', oldKey), oldVal) INTO firstResult;
                SELECT JSON_INSERT(secondResult, CONCAT('$.',oldKey), newVal) INTO secondResult;
            end if;
        ELSE
            SELECT JSON_INSERT(firstResult, CONCAT('$.', oldKey), oldVal) INTO firstResult;
        end if;

        SET i = i + 1;
    end while;
END $$

Use above PROCEDURE to make bidirectional comparison.

DELIMITER $$

DROP PROCEDURE IF EXISTS json_diff_both $$

CREATE PROCEDURE json_diff_both(IN first JSON, IN second JSON, INOUT firstResult JSON, INOUT secondResult JSON)
BEGIN
    CALL json_diff(first, second, firstResult, secondResult);
    CALL json_diff(second, first, secondResult, firstResult);
END $$
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.