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();