mysql intersection_Find intersection of MySQL JSON objects or arrays

问题

The question is about MySQL/MariaDB JSON Functions.

How do you find intersection of multiple JSON structures?

In PHP it is done using this code:

array_intersect(

['a', 'b'],

['b', 'c']

);

If we imagine a function named JSON_INTERSECT, the code would 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;

回答1:

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.

来源：https://stackoverflow.com/questions/44687872/find-intersection-of-mysql-json-objects-or-arrays