How can I use get a value stored in a json column in mysql?

Question

id	lessons
1	["3M-232","3M-313"]
2	["3M-311","3M-312"]
3	["3M-443","3M-565"]
4	["4M-232","4M-313"]
5	["4M-311","4M-312"]
6	["4M-443","4M-565"]

How can get rows that match lessons with "3M-232" as one of them

SELECT * FROM merged_lesson where lessons = "3M-232";

Expected only row 1 to be returned

originally posted as image

Answer 1

You can do it using JSON_TABLE to convert array to rows :

select *
from mytable
cross join JSON_TABLE(lessons, '$[*]'
                      COLUMNS (
                           lesson VARCHAR(40)  PATH '$')
                      ) j
where j.lesson = '3M-232';

Result :

id  lessons                 lesson
1   ["3M-232", "3M-313"]    3M-232

Demo here

Answer 2

We can use JSON_CONTAINS function:

SELECT id, JSON_PRETTY(lessons) AS lessons
FROM tbl_name
WHERE JSON_CONTAINS(lessons, '"3M-232"')

Or, as pointed out by @lemon (ref), it would be much faster to use MEMBER OF:

SELECT id, JSON_PRETTY(lessons) AS lessons
FROM tbl_name
WHERE '3M-232' MEMBER OF(lessons)

demo