Question 167268 in Mysql Question posted in
The official MySQL documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    We can use JSON_CONTAINS function:

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

    demo

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search