8

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;
Yochanan
  • 81
  • 1
  • 5
  • Please explain the structure of you sql tables – Dimgold Jun 25 '17 at 05:45
  • @Dimgold No tables. The question has been updated with more details. – Yochanan Jun 25 '17 at 16:01
  • I believe that sql doesnt parse arrays you need to set a function that compares arraystrings using [regex](https://stackoverflow.com/questions/9099469/mysql-select-like-or-regexp-to-match-multiple-words-in-one-record) or [delimiters](https://stackoverflow.com/questions/3914199/split-strings-using-mysql) – Abr001am Jun 25 '17 at 16:55
  • [Feature was requested](https://jira.mariadb.org/browse/MDEV-26182). Watch and/or vote on the issue to make its priority known. – danblack Jul 20 '21 at 04:14

1 Answers1

4

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.

justanotherguy
  • 506
  • 2
  • 4
  • 18