Mysql 5.7 - select where json

Question

I have some JSON data stored in a filters column. An example from one row:

name     | filters
---------+----------------------------------------------------------
John Doe | [{"Year":2004, "SportID":3}, {"Year":2005, "SportID":27}]

Other rows may have more than 2 objects, this is just an example.

I am trying to select data using a where clause which would iterate the JSON objects and act upon the value. For example, find me records where Year > 2004 or SportID = 24.

It seems like this should work:

SELECT name, filters from my_table
where json_extract(`filters`, "$[*].Year") = 2004;

However I get an empty result set. 🙍‍♂️

Of course I could just return the entire JSON object and parse it in app code, but I'd rather not do that if I don't have to.

UPDATE: As requested, my show create table:

CREATE TABLE `my_table` (
  `userId` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `filters` json DEFAULT NULL,
  PRIMARY KEY (`userId`),
  UNIQUE KEY `my_table_userId_uindex` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

The problem is that JSON_EXTRACT will return values as an array.

See this example output:

mysql> SELECT * FROM my_table;
+--------+-----------+---------------------------------------------------------------+
| userId | name      | filters                                                       |
+--------+-----------+---------------------------------------------------------------+
|      1 | John Doe  | [{"Year": 2004, "SportID": 3}, {"Year": 2005, "SportID": 27}] |
|      2 | Jane Doe  | [{"Year": 2004, "SportID": 4}]                                |
|      3 | Sarah Doe | [{"Year": 2005, "SportID": 1}]                                |
+--------+-----------+---------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT name, filters->"$**.Year" from my_table;
+-----------+----------------------+
| name      | filters->"$**.Year" |
+-----------+----------------------+
| John Doe  | [2004, 2005]         |
| Jane Doe  | [2004]               |
| Sarah Doe | [2005]               |
+-----------+----------------------+
3 rows in set (0.00 sec)

Keep in mind the -> operator is shorthand for JSON_EXTRACT.

Because of this I was able to answer the question "Which people had a row for Year 2004?" by using JSON_CONTAINS like this:

mysql> SELECT name, filters FROM my_table
    -> WHERE JSON_CONTAINS(filters->'$**.Year', '2004');
+----------+---------------------------------------------------------------+
| name     | filters                                                       |
+----------+---------------------------------------------------------------+
| John Doe | [{"Year": 2004, "SportID": 3}, {"Year": 2005, "SportID": 27}] |
| Jane Doe | [{"Year": 2004, "SportID": 4}]                                |
+----------+---------------------------------------------------------------+
2 rows in set (0.00 sec)

Unfortunately that won't let you answer questions with a range, such as "Which people had rows for Year > 2004?".