Find row in table depending on JSON-Object key-value

Question

I have a MySQL database with a table called users. This table has some columns. In a column called actions there are JSON objects stored. For example:

{
  "name": "joe",
  "tasks": [
    {
      "id": "1",
      "type": "wait",
      "value": "s*2*c"
    },
    {
      "id": "2",
      "type": "run",
      "value": "a*c*47I*x"
    }
  ]
}

My aim is to retrieve all the rows that contain JSON objects with a type "run" and which value contains more than one asterisk symbol *. Is this possible considering that the array tasks can have multiple objects in different orders?

Answer 1

You need to use JSON_TABLE() for this:

mysql> select j.* from users cross join
    -> json_table(users.actions, '$.tasks[*]' columns ( type varchar(10) path '$.type', value varchar(20) path '$.value')) as j 
    -> where type='run' and value like '%*%*%';
+------+-----------+
| type | value     |
+------+-----------+
| run  | a*c*47I*x |
+------+-----------+

If you're still using MySQL 5.x, then the best option is to store the data in normal rows and columns if you need to search in this way. That is, don't store the data as JSON.

In fact, that's the best option even if you do have MySQL 8.0. Isn't the following simpler?

select user_id from tasks
where type='run' and value like '%*%*%';