如何在MYSQL的JSON資料陣列中使用WHERE
我有一個簡單的表 myTable 有一個 JSON 資料型別的列。
create table myTable(profile JSON default NULL);
然后我插入了這條記錄
insert into myTable values ('[{"name":"","type":"student","age":""},
{"name":"","type":"teacher","age":"240"},
{"name":"","type":"student","age":"25"},
{"name":"","type":"student","age":"20"}]')
我的問題是我想檢索所有年齡在 20 到 25 歲之間的學生記錄名稱？
可以這樣使用嗎？SELECT name FROM myTable WHERE type = "student' and age BETWEEN 20 &2 5
uj5u.com熱心網友回復：
SELECT jsontable.name, jsontable.age
FROM myTable
CROSS JOIN JSON_TABLE(myTable.profile,
'$[*]' COLUMNS (name VARCHAR(255) PATH '$.name',
`type` VARCHAR(255) PATH '$.type',
age INT PATH '$.age')) jsontable
WHERE jsontable.`type` = 'student'
AND jsontable.age BETWEEN 20 AND 25;
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=559ea1476f933a7ac977537e434f3206
uj5u.com熱心網友回復：
有一章完整介紹了JSON 函式，以及如何使用它們。
select *
from (
select
x.x,
json_unquote(json_extract(profile,concat("$[",x.x,"].name"))) as name,
json_unquote(json_extract(profile,concat("$[",x.x,"].type"))) as type,
json_unquote(json_extract(profile,concat("$[",x.x,"].age"))) as age
from myTable
cross join (select 0 as x union select 1 union select 2 union select 3 union select 4) x
) t
where t.type='student' and t.age between 20 and 25;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/444940.html