Find intersection of MySQL JSON objects or arrays

Question

Question about MySQL / MariaDB JSON Functions .
How to find the intersection of multiple JSON structures?
In PHP, this is done using this code:

array_intersect(
    ['a', 'b'],
    ['b', 'c']
);

If we introduce a function called JSON_INTERSECT, the code will look like this:

SET @json1 = '{"key1": "a", "key2": "b"}';
SET @json2 = '["b", "c"]';
SET @json3 = '["c", "d"]';

SELECT JSON_INTERSECT(@json1, @json2); // returns '["b"]';
SELECT JSON_INTERSECT(@json1, @json3); // returns NULL;

Answer 1

, , - , , . , MY_JSON_INTERSECT, , . , , :

delimiter $$
CREATE FUNCTION `MY_JSON_INTERSECT`(Array1 VARCHAR(1024), Array2 VARCHAR(1024)) RETURNS varchar(1024)
BEGIN
    DECLARE x int;
    DECLARE val, output varchar(1024);
    SET output = '[]';
    SET x = 0;
    IF JSON_LENGTH(Array2) < JSON_LENGTH(Array1) THEN
        SET val = Array2;
        SET Array2 = Array1;
        SET Array1 = val;
    END IF;
    WHILE x < JSON_LENGTH(Array1) DO
        SET val = JSON_EXTRACT(Array1, CONCAT('$[',x,']'));
        IF JSON_CONTAINS(Array2,val) THEN
            SET output = JSON_MERGE(output,val);
        END IF;
        SET x = x + 1; 
    END WHILE;
    IF JSON_LENGTH(output) = 0 THEN
        RETURN NULL;
    ELSE
        RETURN output;
    END IF;
END$$

:

SELECT MY_JSON_INTERSECT('[1,2,3,4,5,6,7,8]','[0,3,5,7,9]');

:

[3,5,7]

, -, ... , .

Answer 2

, :

json_length(@json_array1) + json_length(@json_array2) > json_length(json_merge_patch(@json_array1, @json_array2))

mysql > = 5.7.22