Mysql如何對json資料進行查詢及修改

{"age": "28", "pwd": "lisi", "name": "李四"}

select * from `offcn_off_main` where json_extract(json_field,"$.name") = '李四'

select * from `offcn_off_main` where json_field->'$.name' = '李四' 

fieldname <type> [ GENERATED ALWAYS ] AS ( <expression> ) [ VIRTUAL|STORED ] [ UNIQUE [KEY] ] [ [PRIMARY] KEY ] [ NOT NULL ] [ COMMENT <text> ]

ALTER TABLE 'off_main' `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`json_field` ->> '$.name') not null;

CREATE INDEX `names` ON `off_main`(`names_virtual`);  

update off_main set json_field = json_set(json_field,'$.phone', '132') WHERE id = 45 
//同時修改多個
UPDATE offcn_off_main set json_field = json_set(json_field,'$.name',456,'$.age','bbb') WHERE id = 45 

UPDATE offcn_off_main set json_field = json_remove(json_field,'$.pwd','$.phone') WHERE id = 45 

UPDATE offcn_off_main set json_field = json_insert(json_field,'$.pwd','111') WHERE id = 45 

select *,json->'$.features[0].geometry.rings' as rings from JSON;

insert into DT_village(name, border) SELECT
  json->'$.features[0].attributes.CJQYMC',json->'$.features[0].geometry.rings'
from JSON;

#清空資料庫
TRUNCATE table DT_village;
 
#定義儲存過程
delimiter //
DROP PROCEDURE IF EXISTS insert_test_val;
##num_limit 要插入資料的數量,rand_limit 最大隨機的數值
CREATE PROCEDURE insert_test_val()
  BEGIN
 
    DECLARE i int default 0;
    DECLARE a,b varchar(5000);
 
    WHILE i<10 do
      set a=CONCAT('$.features[',i,'].attributes.CJQYMC');
      set b=CONCAT('$.features[',i,'].geometry.rings');
      insert into DT_village(name, border) select
              #json->'$.features[0].attributes.CJQYMC',json->'$.features[0].geometry.rings'
                                                 # (json->a),(json->b)
   json_extract(json,a),json_extract(json,b)
      from JSON;
      set i = i + 1;
 
    END WHILE;
 
  END
//
 
#呼叫儲存過程
call insert_test_val();

delimiter //
drop procedure if exists StatisticStore;
CREATE PROCEDURE StatisticStore()
  BEGIN
    #建立接收遊標資料的變數
    declare j json;#儲存json資料
    DECLARE i int default 0; #建立總數變數，記錄執行次數，控制迴圈
    DECLARE a,b,c varchar(5000);#定義json陣列中的某個資料的鍵值
 
    #建立結束標誌變數
    declare done int default false;
    #建立遊標
    declare cur cursor for select json from JSON where name = '1';
    #指定遊標迴圈結束時的返回值
    declare continue HANDLER for not found set done = true;
    #設定初始值
    set a=CONCAT('$.features[',i,'].attributes.XZQDM');
    set b=CONCAT('$.features[',i,'].attributes.XZQMC');
    set c=CONCAT('$.features[',i,']');
    #開啟遊標
    open cur;
    #開始迴圈遊標裡的資料
    read_loop:loop
      #根據遊標當前指向的一條資料
      fetch cur into j;
      #判斷遊標的迴圈是否結束
      if done then
        leave read_loop;#跳出遊標迴圈
      end if;
      #這裡可以做任意你想做的操作
      WHILE i<11 do
        insert into dt_border(xzq_code,name,border) select
                                                           json_extract(j,a),json_extract(j,b),json_extract(j,c)
        from JSON;
        set i = i + 1;
      END WHILE;
      #結束遊標迴圈
    end loop;
    #關閉遊標
    close cur;
 
    #輸出結果
    select j,i;
  END;
#呼叫儲存過程
call StatisticStore();