'Find matches in JSON array field in MySQL

Given a JSON object type column in table t, e.g.

| id | obj                                | 
| -- | ---------------------------------- |
| 1  | { "params": { "id": [13, 23]} }    |
| 2  | { "params": { "id": [13, 24]} }    |
| 3  | { "params": { "id": [11, 23, 45]} }|

and a list of numeric values, e.g. [12, 23, 45].

We need to check every record if it contains values from the given list. So, the desired result would be

| id | matches  |
| -- | -------- |
| 1  | [23]     |
| 3  | [23, 45] |

Could someone please help with such a query for the MySQL 8? Thank you!



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source