Find intersection of MySQL JSON objects or arrays

It looks there are no good built-in ways of doing this and there are still no good answers on this topic, so I thought I'd add my quick & dirty solution. If you execute the below code it will create a function called MY_JSON_INTERSECT that will output results exactly as the original poster specified. Make sure you've looked this over and are ok with creating a new function before trusting my code:

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$$

You can then call the function like this:

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

Outputs:

[3,5,7]

This isn't beautiful or efficient, but it's something that works... Hopefully better answers will come soon.