Since you are using MySQL 5.7, you don't have support for the JSON_TABLE() function, which would be the solution for the query you describe.

SELECT j.tag, COUNT(*) AS count
FROM mytable CROSS JOIN 
JSON_TABLE(tags, '$.en[*]' COLUMNS(tag VARCHAR(10) PATH '$.') AS j
GROUP BY j.tag
ORDER BY count DESC;

I would strongly recommend that you store your multi-valued data in normal columns and rows, not in JSON. Then the query would become more straightforward:

SELECT `tag`, COUNT(*) AS count
FROM en_tags
GROUP BY `tag`
ORDER BY count DESC;

Finally, I found the problem.

1. Make sure you check the version if JSON functions are supported.

• MySQL8+
• MariaDB 10.6.0+

In my case, this was easily fixed by JSON_TABLE just like this post "read json array on mysql query"

SELECT 
  dtime, 
  get_activity.* 
FROM 
  logs, 
  JSON_TABLE(
    activity, 
    '$[*]' COLUMNS (
      `id` int(11) PATH '$.ID', 
      `ssl` int(1) PATH '$.SSL', 
      `online` int(1) PATH '$.Online', 
      `cert` text PATH '$.Cert'
    )
  ) get_activity 
WHERE 
  get_activity.id = 3;

All Result:

Result using WHERE filter:

dbfiddle

Yes it is possible to do it using the JSON_EXTRACT and JSON_SEARCH functions.

Let's say your table is named tbl_Jobs and the jobs column is of type JSON.

SELECT * FROM tbl_Jobs
WHERE JSON_EXTRACT(jobs, "$[*].target") = JSON_EXTRACT(jobs, "$[*].done")
AND JSON_EXTRACT(jobs, "$[*].status") != 0
AND JSON_SEARCH(jobs, 'one', DATE_SUB(CURDATE(), INTERVAL 1 DAY), NULL, "$[*].lastAction") IS NOT NULL

There is a way to solve this in SQL 5.7. I will go step by step in composing the solution. The goal is to find the strength of the knight.

I am going to use the same sample table as previous post:

create table mytable ( mycol json );

insert into mytable set mycol = '[{"Race": "Orc", "strength": 14}, {"Race": "Knight", "strength": 7}]';

First, get an array of only the races.

select json_extract(mycol, '$[*].Race') from mytable;
+----------------------------------+
| json_extract(mycol, '$[*].Race') |
+----------------------------------+
| ["Orc", "Knight"]                |
+----------------------------------+

Then, search for the Knight in this array (and unquote it).

select json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight')) from mytable;
+------------------------------------------------------------------------------+
| json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight')) |
+------------------------------------------------------------------------------+
| $[1]                                                                         |
+------------------------------------------------------------------------------+

Having found the index, get this element from the array.

select json_extract(mycol, json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight'))) from mytable;
+---------------------------------------------------------------------------------------------------+
| json_extract(mycol, json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight'))) |
+---------------------------------------------------------------------------------------------------+
| {"Race": "Knight", "strength": 7}                                                                 |
+---------------------------------------------------------------------------------------------------+

Then get the strength of this element.

select json_extract(json_extract(mycol, json_unquote(json_search(json_extract(mycol, '$[*].Race'), 'one', 'Knight'))), '$.strength') as strength from mytable;
+----------+
| strength |
+----------+
| 7        |
+----------+

You can repeat this on other fields to create other columns.