How to emulate JSON_OVERLAPS function on MySQL 5.7?

Question

I have two columns from different tables that hold JSON-formatted data. The data stored in both columns are arrays. Example:

users

+----+------------------+
| id | options          |
+----+------------------+
|  1 | ["AB","CD","XY"] |
|  2 | ["CD","GH"]      |
+----+------------------+

items

+----+-------------+
| id | options     |
+----+-------------+
| 10 | ["CD","EF"] |
| 11 | ["GH","XY"] |
| 12 | ["GH"]      |
+----+-------------+

I wanted to write a query that returns all the rows from users which matches a given row from items, using options columns to perform the match. The rule is if any value in the array is present in both rows, they are a match. Example: user 1 would match items 10 (because of CD option) and 11 (because of XY option); user 2 would match items 10, 11 and 12 because all of them have CD or GH.

Looking at MySQL docs I found that JSON_OVERLAPS does exactly that. However, I'm running MySQL 5.7 and the function is only available starting at 8.0.17. There is also no much talking around this function on the web.

How could I emulate JSON_OVERLAPS behavior on MySQL 5.7 in a query?

Edit: Unfortunately, upgrading to MySQL 8 is not an option since we run MariaDB on production, which also doesn't have that function.

@Strawberry I thought someone would suggest that so I just edited the question to inform that we run MariaDB on production. But thanks, anyway. — Gustavo Straube, Nov 27 '19 at 13:31
i posted a answer, which i believe should also work on MariaDB ~10.2+ (i believe) ... — Raymond Nijland, Nov 27 '19 at 14:15

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

How to emulate JSON_OVERLAPS function on MySQL 5.7?

Edit: Unfortunately, upgrading to MySQL 8 is not an option since we run MariaDB on production, which also doesn't have that function.

Be warned like Strawberry, suggested already upgrading is more easy

Now that is out off the way. You still asked for it, lets have some fun. I posted some answers in the past to simulate MySQL's 8 JSON_TABLE(), why did i mention this? Because i use this method to emulate MySQL's 8 JSON_OVERLAPS to simply JOIN both resultsets which emulate JSON_TABLE() to a final resultset

Which makes the query below (forgive the formatting)

Query

SELECT 
 *
FROM (

SELECT
    items.id
  ,    JSON_UNQUOTE(
       JSON_EXTRACT(items.options, CONCAT('$[', number_generator.number , ']'))
     ) AS json_options
                     
                                
FROM (

  SELECT 
   @items_row := @items_row + 1 AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2
  CROSS JOIN (
    SELECT @items_row := -1 
  ) init_user_params 
) AS number_generator
CROSS JOIN (


SELECT 
    items.id
  , items.options
  , JSON_LENGTH(items.options) AS json_array_length    
FROM 
   items

) AS items 
WHERE
    number BETWEEN 0 AND  json_array_length - 1   
                    
) AS items 
INNER JOIN (

  SELECT
    users.id
  ,    JSON_UNQUOTE(
       JSON_EXTRACT(users.options, CONCAT('$[', number_generator.number , ']'))
     ) AS json_options
                     
                                
FROM (

  SELECT 
   @users_row := @users_row + 1 AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION   SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION  SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) row2
  CROSS JOIN (
    SELECT @users_row := -1 
  ) init_user_params 
) AS number_generator
CROSS JOIN (


SELECT 
    users.id
  , users.options
  , JSON_LENGTH(users.options) AS json_array_length    
FROM 
   users

) AS users 
WHERE
    number BETWEEN 0 AND  json_array_length - 1   
) AS users 
USING(json_options)

Result

| json_options | id  | id  |
| ------------ | --- | --- |
| CD           | 10  | 2   |
| CD           | 10  | 1   |
| GH           | 11  | 2   |
| GH           | 12  | 2   |
| XY           | 11  | 1   |

see demo

Hey! In spite of being a huge query, it worked like a charm! Thanks! — Gustavo Straube, Nov 28 '19 at 18:17
*"In spite of being a huge query"* Well sorry never said it would be easy .. @GustavoStraube , hope you could worked it out how it works.. — Raymond Nijland, Nov 28 '19 at 18:49

How to emulate JSON_OVERLAPS function on MySQL 5.7?

1 Answers1

Linked