Find matches in JSON array field in MySQL-CodePudding

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!

CodePudding user response：

You can use json_table:

select t2.id, t2.n_obj from (
   select t1.id, (select json_arrayagg(ids.v) 
       from json_table(t1.obj, "$.params.id[*]" columns(v text path '$')) ids 
       where json_contains('[12, 23, 45]', ids.v, '$')) 
   n_obj from t t1) t2 
where t2.n_obj is not null;