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

and I change "that match lessons with "2M-232"" to "that match lessons with "3M-232" (to match your SQL statement., see [Proofread before posting!](https://stackoverflow.com/help/how-to-ask#:~:text=Proofread%20before%20posting!) — Luuk, May 13 '23 at 09:46
Sharing data as text, not as image, is preferred, so I copied the text from your image... ,see: [DO NOT post images of code, data, error messages](https://stackoverflow.com/help/how-to-ask#:~:text=DO%20NOT%20post%20images%20of%20code%2C%20data%2C%20error%20messages) — Luuk, May 13 '23 at 09:49

ahmed · Answer 1 · 2023-05-16T13:09:07.843

4

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

edited May 16 '23 at 13:09

answered May 13 '23 at 16:30

ahmed

9,071
3
9
22

1

Check out [`MEMBER OF`](https://stackoverflow.com/questions/59538181/why-is-member-of-faster-than-json-contains) alongside `JSON_CONTAINS`. – lemon May 15 '23 at 13:28
@lemon, Thank you for pointing this out and thank [Bill Karwin](https://stackoverflow.com/users/20860/bill-karwin) for that invaluable post. – ahmed May 16 '23 at 13:12

score 1 · Answer 2 · answered May 13 '23 at 09:37

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

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

2 Answers2