#JSON

CREATE TABLE emp_details(
  emp_no INT PRIMARY KEY
  ,details json
 );
 INSERT INTO emp_details (emp_no,details) VALUES (1,'{"empNumber":1001,"localtion":"IN","phone":"+1180000000","email":"abc@exaple.com","address":{"line1":"abc","line2":"xyz street","city":"beijing","pin":"51000"}}')
 INSERT INTO emp_details (emp_no,details) VALUES (2,'{"empNumber":1002,"localtion":"IN","phone":"+1180000000","email":"abc@exaple.com","address":{"line1":"cde","line2":"xyz street","city":"beijing","pin":"51000"}}')
 INSERT INTO emp_details (emp_no,details) VALUES (3,'{"empNumber":1003,"localtion":"IN","phone":"+1180000000","email":"abc@exaple.com","address":{"line1":"cde","line2":"xyz street","city":"beijing","pin":"51000"}}')

 SELECT EMP_NO 
      ,DETAILS ->  '$.localtion' as localtion   # 要有''  $.  -> 包含双引号
      ,DETAILS ->  '$.address.city' city 
      ,DETAILS ->> '$.email' as email   # ->>  没有双引号
    FROM EMP_DETAILS;  
    
 SELECT EMP_NO , JSON_PRETTY(DETAILS) FROM EMP_DETAILS; #JSON美化
 
 #用JSON对像中的某个key 作为筛选条件  这个与oracle 中筛选集合的某个字段一样的
 SELECT EMP_NO ,JSON_PRETTY(DETAILS) FROM EMP_DETAILS WHERE DETAILS ->> '$.address.line1' ='abc'
 
 #JSON_CONTAINS 判断
 SELECT JSON_CONTAINS(DETAILS ->> '$.adress','abc') from emp_detail;
 SELECT details ->> '$.email' LIKE '%.com%' FROM emp_details;
 SELECT details ->> '$.address.city' = 'beijing' FROM emp_details;
 SELECT details -> '$.address.city' = 'beijing' FROM emp_details;
 SELECT JSON_CONTAINS_PATH(details,'one',"$.address.line1") from emp_details;
 #JSON_CONTAINS
 #1.JSON_CONTAINS(target, candidate[, path])
 # 返回0或1，来判断目标（target）JSON document 是否包含给出的候选（candidate） JSON document 。
 # 如果存在path参数，则将会在path的范围内查找。
 # 如果有任何一个参数是null或者是path参数的表示的路径在target中找不到，则函数会返回null
 SET @J ='{"a":1,"b":"abc","c":{"d":4,"G":1}}';
 SET @J2='1';
 #当path存在时，并且是path的值是某个value，判断的是value
 SELECT JSON_CONTAINS(@J,@J2,'$.a') FROM DUAL;
 #当path参数没有时，candidate必须是相符合的json,判断的是某个key value对
 SET @J2='{"b":"abc"}';
 SELECT JSON_CONTAINS(@J,@J2) FROM DUAL;
 #当path存在时，并且是path的值是某个JSON对像时，判断的是某个key value对
 SET @J2= '{"d":4}';
 SELECT JSON_CONTAINS(@J,@J2,'$.c') from dual;
 #当 path 是目标json中没有的key时 返回null
 SELECT JSON_CONTAINS(@J,@J2,'$.d') FROM DUAL;
 
 
 #当value是数组时，
 SET @J = '{"a": [1,2,3], "b": 2, "c": {"d": 4}}';
 SET @J2= '1';
 SELECT JSON_CONTAINS(@J,@J2,'$.a') FROM DUAL;  #只要是candidate是path对应的value数组中的一个就成立
 #JSON_CONTAINS_PATH(json_doc, one_or_all, path[, path] ...)
 #  查找key是否存在的   
 #  'one': 至少一个path存在则返回1，否则返回0。
 #  'all': 所有path都存在则返回1，否则返回0。
 SELECT JSON_CONTAINS_PATH(@J,'one','$.a') FROM DUAL;
 SELECT JSON_CONTAINS_PATH(@J,'one','$.a','$.d') FROM DUAL;
 SELECT JSON_CONTAINS_PATH(@J,'all','$.a','$.d') FROM DUAL;
 SELECT JSON_CONTAINS_PATH(@J,'all','$.a','$.c.d') FROM DUAL;
 
  SELECT JSON_CONTAINS_PATH(A.DETAILS ,'all' ,'$.address','$.iphone')  AS HAS_ADDRESS_PHONE
      FROM EMP_DETAILS A
     WHERE A.DETAILS ->> '$.address.line1' = 'cde';
  SELECT JSON_CONTAINS_PATH(A.DETAILS ,'one' ,'$.address','$.iphone')  AS HAS_ADDRESS_PHONE
      FROM EMP_DETAILS A
     WHERE A.DETAILS ->> '$.address.line1' = 'cde';     
 #提取json中的某个value
 SELECT JSON_EXTRACT(DETAILS,'$.address') AS address         #  方法1 json_extract
         ,JSON_PRETTY(DETAILS ->> '$.address') AS pretty_address  # 方法二  箭头函数
        ,DETAILS ->> '$.address.line1'  AS line1
      FROM EMP_DETAILS 
     WHERE DETAILS ->> '$.address.line1' LIKE '%d%'
     ORDER BY DETAILS ->> '$.address.line1' DESC
     
     
 
 DROP TABLE IF EXISTS TEST_JOSN;
 CREATE TABLE TEST_JOSN (a JSON, b INT);
 INSERT INTO TEST_JOSN VALUES('[10, 20, [30, 40]]',1)
 SELECT A->'$[2]' FROM TEST_JOSN;
 SELECT JSON_EXTRACT(A, '$[1]') FROM TEST_JOSN;  #当是数组时，下标索引从0开始
 SELECT JSON_EXTRACT(A, '$[2][*]') FROM TEST_JOSN;  #后面这个*可以省略
 SELECT JSON_EXTRACT(A, '$[2]') FROM TEST_JOSN;
 SELECT JSON_EXTRACT(A, '$[2][1]') FROM TEST_JOSN;  #后面这个*可以省略
#查询 
SELECT EMP_NO 
       ,JSON_PRETTY(DETAILS) AS DETAILS
       ,DETAILS->>'$.empNumber' AS empNumber
       ,DETAILS->>'$.address.line1' AS line1
      FROM EMP_DETAILS
     WHERE DETAILS->>'$.address.line1' = 'cde'
     ORDER BY empNumber DESC
 #删除
 DELETE FROM EMP_DETAILS
    WHERE DETAILS -> '$.empNumber' = 1003;
    
 ALTER TABLE EMP_DETAILS ADD EMP_NUMBER INT; 
 #更新
 UPDATE EMP_DETAILS SET EMP_NUMBER = DETAILS -> '$.empNumber';
 
 
 
 SET @TEST_JSON = '["abc", [{"k": "10"}, "def"], {"x":"abc"}, {"y":"bcd"}]';
 SELECT JSON_SEARCH(@TEST_JSON,'one','abc','ab')
 
#修改json 对像  msyql8.0版本 
  #JSON_SET()函数  更新json对像中的某个key对应的value
  UPDATE EMP_DETAILS 
   SET 
      DETAILS = JSON_SET(DETAILS ,"$.email","zenglingtao@myoas.com")
  WHERE DETAILS->>'$.empNumber' = 1001


  #JSON_INSERT()  插入值 但不更新值  #当key现在已有的key时内容不会更新，只有是新key才会插入成功 
  UPDATE EMP_DETAILS
    SET 
       DETAILS =JSON_INSERT(DETAILS,'$.address.line1','update line1','$.address.line3','new line3')
   WHERE DETAILS->>'$.address.line1' ='cde'

 #JSON_REPLACE()  仅替换现在key的内容  只会替换现有key的内容 不会新增key
 UPDATE EMP_DETAILS
    SET
        DETAILS = JSON_REPLACE(DETAILS,'$.address.line1','adc','$.address.line5','new line5')
  WHERE DETAILS->>'$.address.line1' = 'cde'
  
 #JSON_REMOVE()  删除json文档的数据 删除某个key
 UPDATE EMP_DETAILS
   SET
       DETAILS = JSON_REMOVE(DETAILS,'$.address.line1')
 where details->>'$.empNumber' = 1002
 
 
 #JSON_KEYs()  查询json文档中的所有key  第一层
 SELECT JSON_KEYs(DETAILS) 
     FROM EMP_DETAILS
    WHERE DETAILS->>'$.empNumber' = 1002 
 #JSON_LENGTH()  第一层
 SELECT JSON_LENGTH(DETAILS) FROM EMP_DETAILS