select * from 表名 where JSON_VALUE(字段名,'$."json字段名"') like '%查询关键字符串%'
$表示object的根
例 select * from Country where JSON_VALUE(Name,'$."CountryName"') like '%国%'
测试数据
| id(INT) | json(VARCHAR) | path1(VARCHAR) |
|---|---|---|
| 1 | [10, 20, [30, 40]] | $[2][*] |
| 2 | {"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"} | $.ccc.hhh[*] |
| 3 | {"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg",hhh":["h0","h1","h2"]},"iii":"jjj"} | $.ccc.hhh[1] |
| 4 | [10, 20, [30, 40]] | NULL |
| 5 | NULL | $[2][*] |
| 6 | "{xx]" | "$[2][*]" |