MySQL中的JSON函數（二）查詢JSON函數

（二）查詢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"