查找MySQL JSON对象或数组的交集 [英] Find intersection of MySQL JSON objects or arrays
问题描述
问题与 MySQL/MariaDB JSON函数有关.
如何找到多个JSON结构的交集?
在PHP中,可以使用以下代码完成:
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']
);
如果我们想象一个名为JSON_INTERSECT的函数,代码将如下所示:
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;
推荐答案
看来,没有很好的内置方法可以做到这一点,并且在该主题上仍然没有好的答案,所以我想我应该快速补充一下&肮脏的解决方案.如果执行以下代码,它将创建一个名为MY_JSON_INTERSECT的函数,该函数将输出与指定的原始海报完全相同的结果.在信任我的代码之前,请确保您已仔细检查并可以创建一个新函数:
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]');
输出:
[3,5,7]
这不是美观或有效的方法,但它确实有效……希望很快能有更好的答案.
This isn't beautiful or efficient, but it's something that works... Hopefully better answers will come soon.
这篇关于查找MySQL JSON对象或数组的交集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!