Is there a way to check if an json array contains at least one item of another json array in MySQL?

Question

I'm storing some json arrays in my MySQL db json field.
e.g: ["Python", "C", "C++", "C#", "R"]

An example of my db:

    |  name  |      techs       |
    |--------|------------------|
    | victor | ["Python", "R"]  |
    | anne   | ["C#", "Python"] |

I need to search the lines that the json array contains at least one of the items of another json array. The problem is in the query that I'm executing:

   select name from devs  
   where json_contains('techs', '["Python"]')

This actually works fine and returns all the lines that the array contains "Python" (in this example, [Victor, Anne]), but when I try to pass items that don't exists in any of the arrays:

   select * from devs  
   where json_contains('techs', '["Python", "Java"]')

This didn't return nothing, because there isn't an array with "Python" AND "Java" on it. Perhaps, I would like to receive all the lines with "Python" OR "Java" on their json array.

In this way, Is there a syntax to return the data in the way that I want?
Thanks in advance.

Useful information: MySQL: v8.0, Working on Windows 10.

Answer 1

MySQL 8.0 has function JSON_OVERLAPS(), which does exactly what you ask for:

Compares two JSON documents. Returns true (1) if the two document have any key-value pairs or array elements in common.

When two comparing two arrays, JSON_OVERLAPS() returns true if they share one or more array elements in common, and false if they do not.

You can use that in a self-join query, like:

select t.*
from mytable t
inner join mytable t1 on json_overlaps(t1.techs, t2.techs)