sqlserver json 查询

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][*]"