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
SHOW CREATE TABLE
output?