英文:
How can I use get a value stored in a json column in mysql?
问题
| id | lessons |
|---|---|
| 1 | ["3M-232","3M-313"] |
英文:
| 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
答案1
得分: 4
我们可以使用JSON_CONTAINS函数：
SELECT id, JSON_PRETTY(lessons) AS lessons
FROM tbl_name
WHERE JSON_CONTAINS(lessons, ''"3M-232"')
或者，正如@lemon指出的(ref)，使用MEMBER OF会更快：
SELECT id, JSON_PRETTY(lessons) AS lessons
FROM tbl_name
WHERE ''3M-232'' MEMBER OF(lessons)
英文:
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)
答案2
得分: 1
你可以使用 JSON_TABLE 将数组转换为行来实现：
select *
from mytable
cross join JSON_TABLE(lessons, '$[*]'
COLUMNS (
lesson VARCHAR(40) PATH '$')
) j
where j.lesson = '3M-232';
结果：
id lessons lesson
1 ["3M-232", "3M-313"] 3M-232
英文:
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
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论