Select query where exists intersection of two list

Question

I have one table that contains two columns:

1. id (integer)
2. list_colum (longtext) - contains json array (e.g. [1, 2, 3])

I want to select all records that have an intersection of my input list.

Let me explain:

My input is a list - [2, 3]

Pseudo code:

SELECT * 
FROM table
WHERE  intersection of [2, 3] and table.list_column is not empty list;

Is it possible to do this in SQL?

I am using the newest version of MariaDB.

Answer 1

Edit: The answer doesn't give a true intersection: it only checks if the given JSON array is the intersection and not whether there is an intersection between it and the stored value.

---

JSON_CONTAINS should work:

MariaDB [(none)]> SET @json = '[1,2,3,4,5,6]';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '[1, 3, 5]');
+-----------------------------------+
| JSON_CONTAINS(@json, '[1, 3, 5]') |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '[1, 2, 3]');
+-----------------------------------+
| JSON_CONTAINS(@json, '[1, 2, 3]') |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '[7]');
+-----------------------------+
| JSON_CONTAINS(@json, '[7]') |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> SELECT JSON_CONTAINS(@json, '[5, 6, 7]');
+-----------------------------------+
| JSON_CONTAINS(@json, '[5, 6, 7]') |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.00 sec)