0

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
  • Hi, can you post the SHOW CREATE TABLE output? – Derek Downey May 17 at 19:02
  • @DerekDowney added – Brian Morton May 17 at 19:42
0

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?".

  • Thank you for your answer - but that is the crux of this whole thing. I can't explicity specify the values, I need >,< and IN (...) :( – Brian Morton May 17 at 21:33
  • Right, I understand. >, <, etc would work if it weren't an array return value. IN and BETWEEN aren't available even in 8.0 (near the bottom in the Comparison and Ordering of JSON values) – Derek Downey May 17 at 22:02
  • Thank you very much for your help. It seems like I will have to find another way (either through appcode or restructuring the data). – Brian Morton May 21 at 1:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.