（二）查询JSON函数
JSON_CONTAINS() #查询文档中是否包含指定的元素
JSON_CONTAINS_PATH() #查询文档中是否包含指定的路径
JSON_EXTRACT() #根据条件提取文档中数据
JSON_KEYS() #提取所有key的集合
JSON_SEARCH() #返回所有符合条件的路径集合
1.JSON_CONTAINS()
SELECT JSON_CONTAINS(‘[1,2,3,"abc",null]‘,‘"abc"‘) SELECT JSON_CONTAINS(‘[1,2,3,"abc",null]‘,‘10‘)
SELECT JSON_CONTAINS(‘[1,2,3,"abc",null]‘,‘[1,3]‘)
2. JSON_CONTAINS_PATH()
SELECT JSON_CONTAINS_PATH(‘{"k1":"jack","k2":"tom","k3":"lisa"}‘,‘one‘,‘$.k1‘,‘$.k4‘) one_path
SELECT json_contains_path(‘{"k1":"jack","k2":"tom","k3":"lisa"}‘,‘all‘,‘$.k1‘,‘$.k4‘) all_path
3.JSON_EXTRACT()
SELECT json_extract(‘[10,20,[30,40]]‘,‘$[0]‘,‘$[1]‘)
SELECT json_extract(‘[10,20,[30,40]]‘,‘$[0]‘,‘$[1]‘),json_extract(‘[10,20,[30,40]]‘,‘$[2]‘),json_extract(‘[10,20,[30,40]]‘,‘$[2][*]‘)
SELECT id1,id1->"$[0]",id1->"$[1]" FROM t1 WHERE id1->"$[0]"=10
4.JSON_KEYS()
SELECT JSON_KEYS(‘{"a":1,"b":{"c":30}}‘),JSON_KEYS(‘{"a":1,"b":{"c":30}}‘,‘$.b‘)
5.JSON_SEARCH()
SELECT json_search ( ‘{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}‘, ‘one‘, ‘t%‘ ) ONE, json_search ( ‘{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}‘, ‘all‘, ‘t%‘ ) one_or_all, json_search ( ‘{"k1":"jack","k2":"tom","k3":"lisa","k4":"tony"}‘, ‘all‘, ‘t%‘ ) "all"
原文：https://www.cnblogs.com/kate7/p/13281585.html